CODEWITHSUNDEEP
phpyii2
dr Anton
dr Anton

Reputation: 124

Yii2 how to add clause where > 0 to the relational attribute

I have two models: Users, Posts. User has many posts.

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

I need to get only those users who have posts (posts>0). How do I write query?

Users:find()->where(['>', 'posts', 0])->all()

The code above doesn't work.

Upvotes: 1

Views: 57

Answers (3)

rob006
rob006

Reputation: 22144

To get users with at least one post you need to use INNER JOIN:

Users::find()
    ->innerJoinWith('posts', false)
    ->groupBy('users.id')
    ->all();

It should be more efficient that subquery.

If you want to filter by number of posts, you should add HAVING clause with count condition:

Users::find()
    ->innerJoinWith('posts', false)
    ->groupBy('users.id')
    ->having(new \yii\db\Expression('COUNT(*) > :posts_nr', ['posts_nr' => 2]))
    ->all();

But such queries may be really heavy on large databases - you should consider adding additional column with posts count to users table.

Upvotes: 1

Sakezzz
Sakezzz

Reputation: 468

$orders = Users::find()->with('posts')->all();

Inner join should remove users where posts are null

Upvotes: -1

Yasin Patel
Yasin Patel

Reputation: 5721

Try This query :

Users:find()
->where('id IN (select user_id FROM posts GROUP BY user_id)')
->all();

Upvotes: 1

Related Questions