Efficient way of counting related models of an active record

Yii 2.0 General Discussions
1

Let’s consider we have two ActiveRecords Post and Comment where each post may have multiple comments (one-to-many)

If we wanted to load a list of Posts and get the number of Comments for each, the trivial way would be:


$posts = POST::find()->where(...)->all();
foreach($posts as $post){
...
... $post->getComments()->count(); //getComments return hasMany activeQuery
...
}

The problem with this approach is that the Select COUNT(*) ... query will be executed for each posts, and it would be ideal if there was a way to use the with() function to load the count of comments in one query similar to how it loads the list of related models.

To do this we created a new function in the Post model:

public function getCommentsCount()
{
    $query = (new ActiveQuery(Comment::className()));
    $query->select(['count(*) as count', 'post_id']);
    $query->primaryModel = $this;
    $query->link = ['post_id' => 'id'];
    $query->multiple = false;
    $query->groupBy(['post_id']);
    $query->asArray();
    return $query;
}

then when loading posts we do $posts = POST::find()->where(...)->with(['commentsCount'])->all();

this grant us access to comments count with yii doing all the work in the background while also doing one* query only to load the comments count for each post

foreach($posts as $post){
...
... $post->commentsCount['count'];
...
}

Hope this helps someone and let us know if we can further optimize this.

3 Likes