Thank you Cricket, I will try and look in to the counterCache. 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. > > > http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#belongsto > > > On Wed, May 29, 2013 at 5:39 AM, heavyKevy <arnett...@gmail.com<javascript:> > > 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 <javascript:>. >> To post to this group, send email to cake...@googlegroups.com<javascript:> >> . >> 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+unsubscr...@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.
