CakePHP 2.* how to use custom complex sql Query with model

Let's suppose that we have MySQL database with table categories:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(64) DEFAULT NULL,
  `description` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


Then, in CakePHP framework, you want to call a simple query as you did it with mysql_query in PHP with no framework.

For that you have the class Model::query()

First, you build the model. You don't want to use predefined Models for a table (like the model Categories for DB table Categories). Because you want to use perhaps this query for extracting data from two tables which are related.

So,  first you have to create the model. For this you have to create the file APP/Model/Categs.php



class Categ extends AppModel {

    public $useTable = false; // it is mandatory to have this line. Whithout this line, 
                              //                                    the Controller will search for table Categ; it doesn't matter that you have a sql query  

    public function getDataFromMyTable(){
        $d = $this->query("SELECT * FROM  categories");
        return $d;
    }

}


Second, you have to create the Controller. The file will be: APP/Controller/CategsController.php


class CostsController extends AppController {
    
    
    public function index(){
        $this->loadModel('Categ');
    
        $rezQuery = $this->Categ->getDataFromMyTable();
        $this->set('cs', $rezQuery);
    }
}
?>


And now we have to create the view. We create folder APP/View/Categs (if this folder doesn't exists). And then we create the file index.ctp into this folder. The name of the ctp file should be the  same as name of the function from Controller. In index.ctp we write: