CODEWITHSUNDEEP
mysqlactiverecordyii2relationships
user1954544
user1954544

Reputation: 1687

Yii2, ActiveQuery, subQuery have not entry with ID

I cannot found solution... We have mysql tables

`users`
id | username
1      Mike
2      John
3      Jane

`roles`
userID | roleID
1         2
1         3
3         1

So question is - I need select all users that have NOT specific roleID in one query. Also user can HAVE NOT any relations in roles (for example John have not any roleID so he should be selected in all queries).

Simple query is when user have some roleID (we can check it with ->joinWith()) - but I need invert this query, select all users that have any other except one roleID.

Problem is that we have oneToMany (users->roles) relations and Yii2 ->joinWith() is not working in correct way (as I think, I hope I'm wrong).

Can someone help to deal with it?

Upvotes: 0

Views: 236

Answers (2)

Kiran Muralee
Kiran Muralee

Reputation: 2060

If you want to select all users who do not have any roles, then simple SQL query to the above problem is

SELECT username 
FROM users 
WHERE id NOT IN (SELECT userID FROM roles)

The result will return "john" as he is not having any role

In Yii2, you can write the code to generate above query as 

$subQuery=Roles::find()->select('userID ');

$query = Users::find()->
select("username")->where(['not in', 'id', $subQuery]);

$models=$query->all();

Upvotes: 0

Fabrizio Caldarelli
Fabrizio Caldarelli

Reputation: 3008

If User model has 'roles' relation and $excludedRoleId is the roleId excluded, this returns Users without roles or Users which roles is not $excludedRoleId

User::find()
->joinWith(['roles'])
->where('roles.userID IS NULL OR roles.userID NOT IN ( SELECT userID FROM roles WHERE roleID = :roleId)', [':roleId' => $excludedRoleId])
->all();

Upvotes: 1

Related Questions