get count in relation table in yii2 Activerecord

Question

I have two table for post and user. I want to show post count of user in users list gridview. In yii 1 I use this in model to define a relation for this purpose:

'postCount' => array(self::STAT, 'Post', 'author',
            'condition' => 'status = ' . Post::ACTIVE),

...
User:find...().with('postCount').....

But i don't know how to implement this in Yii2 to get count of post in User:find():with('...') to show in gridview.
Anyone try this in yii2?

Answer 1

Here is an example of what I did and it seems to work fine so far. It was used to get a count of comments on a post. I simply used a standard active record count and created the relation with the where statement using $this->id and the primary key of the entry its getting a count for.

public function getComment_count()
{
    return Comment::find()->where(['post' => $this->id])->count();
}

Just a passing it along...

Answer 2

You can try the code below:

User::find()->joinWith('posts',true,'RIGHT JOIN')->where(['user.id'=>'posts.id'])->count();

Or if you want to specify a user count:

//user id 2 for example
User::find()->joinWith('posts',true,'RIGHT JOIN')->where(['user.id'=>'posts.id','user.id'=>2])->count();

Please note that, posts is a relation defined in your User model like below:

public function getPosts()
{
    return $this->hasMany(Post::className(), ['user_id' => 'id']);
}

Answer 3

Well still I think for those who it may concern, if you JUST want the count a select and not the data it will be better use this instead imho:

$count = (new \yii\db\Query())
->select('count(*)')
->from('table')
->where(['condition'=>'value'])
->scalar();

echo $count;