Why don't cakePHP include all my associations into one SQL query? The only way I have been able to do this is using "joins", but I hoped belongsTo and Containable behaviour was enough.
Here is an example: Post->belongsTo->Category->belongsTo->category_type (All models are setup correctly and work.
listing posts with pagination in index, I try this:
public function index() {
$this->paginate = array( 'contain' => array('Category' => array('CategoryType')));
$this->set('posts', $this->paginate());
}
This fetches the array correctly, but it does it in many SQLs like this:
SELECT `Post`.`id`, `Post`.`name`, `Post`.`content`, `Post`.`category_id`, `Category`.`id`, `Category`.`name`, `Category`.`category_type_id` FROM `unit_app`.`post` AS `Post` LEFT JOIN `unit_app`.`categories` AS `Category` ON (`Post`.`category_id` = `Category`.`id`) WHERE 1 = 1 LIMIT 20
SELECT `CategoryType`.`id`, `CategoryType`.`name` FROM `unit_app`.`category_types` AS `CategoryType` WHERE `CategoryType`.`id` = 1
SELECT `CategoryType`.`id`, `CategoryType`.`name` FROM `unit_app`.`category_types` AS `CategoryType` WHERE `CategoryType`.`id` = 2
SELECT `CategoryType`.`id`, `CategoryType`.`name` FROM `unit_app`.`category_types` AS `CategoryType` WHERE `CategoryType`.`id` = 2
This makes it difficult to order this query on CategoryType.name ASC. Any suggestions?
If joins are only option, do I have to unbind the models before querying? Will pagination work fine with joins?
Note! this is just a small part of all models, the resulting post->index need to fetch many other models through similar associations also.
(tested on cake 2.2.0 and v2.4.0-dev, php v5.4.11)
UPDATE! ---------
I just wanted to show my findings. I have now solved this without joins, but I had to re-bind in the model to get it working.
This is basically what I did to get it to work (also with paginations and sorts): In Post model: Added a bind function:
$this->unbindModel(array(
'belongsTo' => array('Category')
));
$this->bindModel(array(
'hasOne' => array(
'Category' => array(
'foreignKey' => false,
'conditions' => array('Category.id = Post.category_id')
),
'CategoryType' => array(
'foreignKey' => false,
'conditions' => array('CategoryType.id = Category.category_type_id')
))));
Then I added this to my index in Post controller:
$this->Post->bindCategory();
$this->paginate = array('contain' => array('Category' ,'CategoryType'));
$this->set('posts', $this->paginate());
I include table headers also just for documentation:
<th><?php echo $this->Paginator->sort('CategoryType.name', 'Type'); ?></th>
<th><?php echo $this->Paginator->sort('Category.name', 'Category'); ?></th>
I hope this post can help others as well :)
I am also going to test this Behaviour to see if I can omit all the bind-functions as well: https://github.com/siran/linkable/
There are lots of plugins to cake, but cake should have a "certification" of the plugins. It is quite difficult to find the fully working and tested ones on github :)
I also miss a site like railscasts.com just for cake :D
/MartOn
CREATE VIEW post_with_category AS ...
and use that as a database-source for your model. This way your CakePHP model can be kept simple and the database view will act as a 'abstraction layer'. dev.mysql.com/doc/refman/5.0/en/create-view.html IMO try to use CakePHP queries where possible, but don't force yourself into using them if things get so complicated that it will only make your code more difficult to understand and maintain. – thaJeztah