Efficient way of counting related models of an active record

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