CODEWITHSUNDEEP
yiiyii2yii2-model
ingenious
ingenious

Reputation: 772

Yii2, find users that has active post

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.

Upvotes: 1

Views: 409

Answers (3)

HeadwindFly
HeadwindFly

Reputation: 884

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();

Upvotes: 1

Vitaly
Vitaly

Reputation: 1281

Best way:

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

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

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

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

Upvotes: 0

Related Questions