On Sunday, June 2, 2013 12:01:40 AM UTC+7, cricket wrote:
--ContainableBehavior doesn't work like that, unfortunately. It's a really good addition to the toolbox, though at the cost of the extra queries. (I use it a lot, but only where I can cache the results.) What you should be doing is either adding the 'joins' option, or using counterCache. If all you want is a count of the comments when listing your posts then the latter is a good solution.On Wed, May 29, 2013 at 5:39 AM, heavyKevy <arnett...@gmail.com> wrote:
I am trying to extend the blog example where my Posts belong to Users and Posts has many comments and to get a query where the comments are left joined into the query such that I can get a comment count for each post for listing out in my index page.
According to the documentation, it should be able to work, but after trying nearly everything I could find in the documentation including changing recursive to 1 and using contain, the paginate call to find_all still refuses to include comments in the join list such that I get an error that the comments.id was not found.
When I get rid of the count, fields, and contain and set recursive to 1, it generates 2 separate queries, the first left joining the posts with the users, the second querying the comments.
Is there something I am missing?
This is the Query I am expecting:
SELECT `Post`.`id`, `Post`.`user_id`, `Post`.`title`, `Post`.`post`, `User`.`id`, `User`.`role`, `User`.`username`, `User`.`password`, `User`.`email`, `User`.`Avatar`, `User`.`first_name`, `User`.`last_name`, `User`.`created`, COUNT(`Comment`.`id`) comments FROM `blog`.`Posts` AS `Post` LEFT JOIN `blog`.`Comments` AS `Comment` ON (`Comment`.`Post_id` = `Post`.`id`) LEFT JOIN `blog`.`users` AS `User` ON (`Post`.`user_id` = `User`.`id`) WHERE 1 = 1 GROUP BY `Comment`.`post_id`;
This is what is produced:
SELECT `Post`.`id`, `Post`.`user_id`, `Post`.`title`, `Post`.`post`, `User`.`id`, `User`.`role`, `User`.`username`, `User`.`password`, `User`.`email`, `User`.`Avatar`, `User`.`first_name`, `User`.`last_name`, `User`.`created`, COUNT(`Comment`.`id`) comments FROM `blog`.`Posts` AS `Post` LEFT JOIN `blog`.`users` AS `User` ON (`Post`.`user_id` = `User`.`id`) WHERE 1 = 1 GROUP BY `Comment`.`post_id`;
using this code:
$this->Question->recursive = 1;
$this->set('userId', $this->Auth->user('id'));
$this->Question->Behaviors->load('Containable', array('autoFields' => false));
$this->paginate =array(
'fields' => array('Question.id', 'Question.user_id', 'Question.title', 'Question.question',
'User.id', 'User.role', 'User.username', 'User.password', 'User.email', 'User.Avatar',
'User.first_name', 'User.last_name', 'User.created', 'COUNT(Comment.id) answers'
),
'group' => 'Comment.question_id',
'contain' => array('User','Comment.id'));
$this->set('questions', $this->paginate());
Is the only way to get it to work without using my own SQL to specify all the fields and force the joins?
Adding Joins to the options, I can force the join and it works like I want, but shouldn't it be able to figure out the joins by itself with the contain specifying the comment table?
Code for options with forced Join for the comments table:
$this->paginate =array(
'fields' => array('Question.id', 'Question.user_id', 'Question.title', 'Question.question',
'User.id', 'User.role', 'User.username', 'User.password', 'User.email', 'User.Avatar',
'User.first_name', 'User.last_name', 'User.created', 'COUNT(Comment.id) answers'
),
'group' => 'Comment.question_id',
'joins' => array(array(
'table'=>'Comments', 'alias'=>'Comment', 'type'=>'LEFT',
'conditions'=>array('Comment.question_id = Question.id'))) ,
'contain' => array('User','Comment.id'));
--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+u...@googlegroups.com .
To post to this group, send email to cake...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en .
For more options, visit https://groups.google.com/groups/opt_out .
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
No comments:
Post a Comment