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+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.
