Re: COUNT HABTM whith condition

@mscdex:
I already tried this. I get an "Unknown column 'Post.created' in
'where clause'!" error. That's because cake first fetches the records
from users and posts_users and then makes a second query to get the
related posts. So putting your condition in the find('all') can't
work, as the Post.created is not available in the first query.

SOLUTION:
I finally got it working. Here is what I did:

$this->User->PostsUser->bindModel(array('belongsTo' => array
('User'))); //I'm using $this->User->PostsUser, because I'm in the
users_controller
$this->User->PostsUser->bindModel(array('belongsTo'=>array('Post')));

$users = $this->User->PostsUser->find('all', array(
'conditions'=>array('Post.created >'=>$someDate),
'recursive' => 1, //int
'fields' => array('*','COUNT(PostsUser.post_id) AS num,
Post.created'),
'group' => 'User.id',
'order' => 'num DESC',
'limit' => 10 //int
));

So I'm just starting from the join model, that is bound to two
belongsTo relations. Then the whole data is fetch with a single sql
query

Hope this helps someone.

Donnerbeil
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---