Search code examples
mysqlactiverecordyii2relationships

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?

Solution

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