How to do count query inside eager loading

Yii 2.0 General Discussions
1

I am getting data from 3 tables thanks to yii2 eager loading. But also, I need to run one COUNT() function to count number of returned rows from one table. If I run it inside select() method, my query will fail saying "Trying to get property of non-object" if I try to display result from one of the joined tables.

This is my normal query:




$comments = Comment::find()->where(['in', 'post_id', $posts])

                           ->with('post')

                           ->with('user')

                           ->all();

I want to count number of post_id in my comment table, executed by this query. ( I want to get comments along with posts that they belong too and users that made those comments, plus count number of posts ( defined by $posts array ) that have comments.

I have tried with this but failed:




$comments = Comment::find()->select(['COUNT(user_id) AS count'])

                           ->where(['in', 'post_id', $posts])

                           ->with('post')

                           ->with('user')

                           ->all();

Do anyone know how I can do this ?

1 Like
2

Anyone ?

3

You wrote "AS count". You could try to write "AS counting", because "count" is a reserved SQL keyword. Then you have to create the "counting" attribute in the class Comment:




class Comment{

public $counting;


//...

}
4

If I do it like that now I get another error : Trying to get property of a non object, most likely because my query hasn’t selected necessary fields from related tables, and I do not know how to disambiguate them.




$comments = Comment::find()->select(['COUNT(user_id) AS userCount'])

                           ->where(['in', 'post_id', $posts])

                           ->with('post')

                           ->with('user')

                           ->all();

If I try to access post->title, I will get error.


foreach ($comments as $data) {

    echo $data->post->title; // error - unknown column in the field list

}

If I try to select like this I will get sql error because I do not know how to disabiguate fields that have the same name in all 3 tables ( for example id, or title )




->select(['post.id', 'post.title', 'COUNT(user_id) AS userCount'])

How I am supposed to do this ?

5

If I use joinWith insted of with and manually select all, I will get only one result returned and there should be more:




$comments = Comment::find()->select(['user.*', 'post.*', 'comment.*', 'COUNT(user_id) AS userCount'])

                           ->where(['in', 'post.post_id', $posts])

                           ->joinWith('post')

                           ->joinWith('user')

                           ->all();

What am I doing wrong ? This is killing me…

6

Changing ->all() to ->count() should do the job, this is what i practically use for my exists() now


$comments = Comment::find()->select(['user.*', 'post.*', 'comment.*', 'COUNT(user_id) AS userCount'])

                           ->where(['in', 'post.post_id', $posts])

                           ->joinWith('post')

                           ->joinWith('user')

                           ->count();