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.

Answer 2

I had the same question. The answer that PawelMysior gave was great and lead to the solution.

I found some more details in the CakePHP Book: 3.7.4.1.1 counterCache - Cache your count(). This was helpful but still a bit vague. For the sake of others, I want to provide some further details.

I have two tables: Post and Image. When I added an image to a post I want to keep track of how many images each post had for display on the Post index list, without needing to run an extra count query on the Image table.

CREATE TABLE posts
(
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INTEGER UNSIGNED NOT NULL,
title VARCHAR(255),
body TEXT,
created DATETIME,
modified DATETIME,
image_count INTEGER UNSIGNED,
PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE images
(
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
post_id INTEGER UNSIGNED NOT NULL,
filename VARCHAR(255),
created DATETIME,
modified DATETIME,
PRIMARY KEY (id)
) ENGINE=InnoDB;

Notice that the image_count goes in the posts table. Nothing special is needed in the images table.

class Post extends AppModel
{
    var $name = 'Memory';
    var $hasMany = array(
        'Image' => array(
            'className' => 'Image',
            'foreignKey' => 'post_id',
            'dependent' => false
        )
    );
}

class Image extends AppModel
{
    var $name = 'Image';
    var $belongsTo = array(
        'Post' => array(
            'className' => 'Post',
            'foreignKey' => 'post_id',
            'counterCache' => true
        )
    );
}

Notice that counterCache is added to the Image model. Nothing special is needed in the Post model.

$this->Post->Behaviors->attach('Containable');
$post = $this->Post->find('all', array(
    'conditions' => array('user_id' => 7),
    'order' => array('Post.created DESC'),
    'contain' => array(
        'Post' => array(
            'User' => array('first_name', 'last_name')
        )
    )
));

Now when we do a find there is no need to do anything special to find the count because it is automatically a field in the Post results. Notice the image_count below.

Array
(
[Post] => Array
        (
            [0] => Array
                (
                    [id] => 14
                    [user_id] => 7
                    [title] => Another great post
                    [body] => Lorem ipsum...
                    [created] => 2011-10-26 11:45:05
                    [modified] => 2011-10-26 11:45:05
                    [image_count] => 21
                    [User] => Array
                    (
                        [first_name] => John
                        [last_name] => Doe
                    )
                )
        )
)

One thing to note, if you add counterCache to an existing database structure, the counts in Post will be zero until another Image is added. At the point, CakePHP will do an actual count and update the image_count to the correct amount.

I hope this extra information is helpful for someone.