CakePHP Model: COUNT(*) in Containable

Question

I have a CakePHP 1.3 app and really enjoy the Containable behavior for fetching data.

Let's assume I have Posts in one-to-many relationship with Comments. I use Containable to query (for pagination) a list of all Posts and the belonging Comments. But I'm only interested in how many Comments each Post has. I did not found any way to achieve this query with containable without fetching all rows of Comments. I tried:

$this->paginate=array(
            'fields' => 'Post.title, Post.created',
            'contain' => array('Comment'=>'COUNT(*) AS count'),
        );

results in 'Model "Comment" is not associated with model "Count"' error message.

$this->paginate=array(
            'fields' => array('Post.title, Post.created'),
            'contain' => array('Comment'=>array('fields'=>'COUNT(*) AS count'),
        );

does not work, result set contains for each Post an empty Comment array except for the last one, where it contains the count field, but having the number of all comments not just the belonging ones.

My other guess was

$this->paginate=array(
            'fields' => 'Post.title, Post.created, COUNT(Comment.id)',
            'contain' => array('Comment'=>array('fields'=>''),
        );

but this results in an error, because hasMany relationships are queried independently, so the Answer table is not in the query for the Post entries. How can I count the number of Comments a Post has?

Answer 1

Well, the best way to do this is to set up a field called comment_count in the posts table and add this key to the Comment model $belongsTo Post array:

'counterCache' => true

Every time anything will be happening with the comments, the comment_count field in the related post will be updated (actually, it recounts every time, instead of just adding or deleting).

It's better, because when you fetch data for the user, its way faster and doesn't even touch the comments table. Since you're using the Containable behaviour I guess speed and lightweight is what you're looking for.