Is there a way to get a 'has many' relationship left joined without forcing the join such that I can get a count of the child table?

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