Yii2, find users that has active post

Question

I have user and post table with oneToMany relation:

In Post Model:

public function getUser() {
    return $this->hasOne(User::className(), ['id' => 'user_id']);
}

And in User Model:

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

I want find All users that has at least a active post. But I don`t have any idea.

My target SQL :

SELECT * FROM `user`
where id in (select user_id from post where status = 1)

What do I do?

User::find()->where(...

notice: its important that created with find(), because I want use it in search model

Thanks.

score 1 · Answer 1 · answered Jun 16 '16 at 17:53

1

Best way:

User::find()->joinWith(['posts' => function(ActiveQuery $q){
    $q->where(['status' => 1]);
}])->all();

answered Jun 16 '16 at 17:53

Vitaly

1,261
2
10
20

HeadwindFly · Accepted Answer · 2016-06-16T23:24:31.440

1

You need add a condition using where.

It is recommend that(It query speed is faster than other):

$condition = <<<EOL
EXISTS (
       SELECT 1 from post t2 
       WHERE (t1.id = t2.user_id) 
              AND (t2.status = 1)
)
EOL;

User::find()->from(['t1'=>User::getTableSchema()->name])
    ->where(new yii\db\Expression($condition))
    ->all();

edited Jun 16 '16 at 23:24

answered Jun 16 '16 at 23:13

HeadwindFly

774
3
8
19

The ActiveQuery::alias method can be used instead of the `from(['t1'=>User::getTableSchema()->name])` ==>> `User::find()->alias('t')` – ingenious Jun 19 '16 at 10:33
@ingenious Thank you for your complement :). – HeadwindFly Jun 19 '16 at 10:45

score 0 · Answer 3 · answered Jun 16 '16 at 17:38

0

You can use literal where however using User::find()

 User::find()->where('id in (select user_id from post where status = 1)');

answered Jun 16 '16 at 17:38

ScaisEdge

131,976
10
91
107

Yii2, find users that has active post

3 Answers3