mySQL select from one table where does not exists in another, and is not a child in a third table

Question

I'm using Yii RBAC to control user access to my app, it consists of three mySql tables;

authitem (the RBAC role items)

authitemchild (any RBAC rules that belong to other roles)

authitemassignment (assigning roles to a user)

For example we may have tables like so:

authitem:

|   name (pk)   |
 areaASuperUser
 areaACreateOnly
 areaAReadOnly
 areaAUpdateOnly
 areaADeleteOnly

 areaBSuperUser
 areaBCreateOnly
 areaBReadOnly
 areaBUpdateOnly
 areaBDeleteOnly

authitemchild

|   parent (pk)   |   child (pk)   |
 areaASuperUser    areaACreateOnly
 areaASuperUser    areaAReadOnly
 areaASuperUser    areaAUpdateOnly
 areaASuperUser    areaADeleteOnly

 areaBSuperUser    areaBCreateOnly
 areaBSuperUser    areaBReadOnly
 areaBSuperUser    areaBUpdateOnly
 areaBSuperUser    areaBDeleteOnly

auithitemassignment

|    itemname (pk)   |   userid (pk)   |
 areaASuperUser       1

In the above scenario, user with id of 1 has complete CRUD access in areaA. What I need is a list of all the roles the user does not have access too, and I need to take into account the children of any roles they have access too as well.

I can easily grab all the roles the user doesn't have access to:

SELECT DISTINCT `ai`.`name`
FROM `authitem` `ai`
LEFT JOIN `authassignment` `aa`
ON `aa`.`itemname` = `ai`.`name` AND `aa`.`userid` = 1
WHERE  `aa`.`itemname` IS NULL

But this returns:

 |   name    |
 areaACreateOnly
 areaAReadOnly
 areaAUpdateOnly
 areaADeleteOnly

 areaBSuperUser
 areaBCreateOnly
 areaBReadOnly
 areaBUpdateOnly
 areaBDeleteOnly

And because all the areaA* roles are children of areaASuperUser, I don't want them returned.

Any advice or a push in the right direction would be greatly appreciated!

* Edit:

Thanks @SuVeRa, your answer:

SELECT DISTINCT `ai`.`name`
FROM `authitem` `ai`
LEFT JOIN `authassignment` `aa`
ON `aa`.`itemname` = `ai`.`name` AND `aa`.`userid` = 1
WHERE  
`aa`.`itemname` IS NULL 
AND `ai`.`name` NOT IN (
    SELECT
    `aic`.`child` itemname
    FROM `authitemchild` `aic`
    JOIN `authassignment` `aa`
    ON `aa`.`itemname` = `aic`.`parent`
    WHERE `aa`.`userid` = 1
)

works perfectly for the example above, however I've just extended the app to include more than one level for children, i.e.

authitem:

|   name (pk)   |

 areaABSuperUser

 areaASuperUser
 areaACreateOnly
 areaAReadOnly
 areaAUpdateOnly
 areaADeleteOnly

 areaBSuperUser
 areaBCreateOnly
 areaBReadOnly
 areaBUpdateOnly
 areaBDeleteOnly

 areaCSuperUser
 areaCCreateOnly
 areaCReadOnly
 areaCUpdateOnly
 areaCDeleteOnly

authitemchild

|   parent (pk)   |   child (pk)   |
 areaABSuperUser   areaASuperUser
 areaABSuperUser   areaBSuperUser

 areaASuperUser    areaACreateOnly
 areaASuperUser    areaAReadOnly
 areaASuperUser    areaAUpdateOnly
 areaASuperUser    areaADeleteOnly

 areaBSuperUser    areaBCreateOnly
 areaBSuperUser    areaBReadOnly
 areaBSuperUser    areaBUpdateOnly
 areaBSuperUser    areaBDeleteOnly

 areaCSuperUser    areaCCreateOnly
 areaCSuperUser    areaCReadOnly
 areaCSuperUser    areaCUpdateOnly
 areaCSuperUser    areaCDeleteOnly

auithitemassignment

|    itemname (pk)   |   userid (pk)   |
 areaABSuperUser      1

The original answer would return;

 |   name    |
 areaACreateOnly
 areaAReadOnly
 areaAUpdateOnly
 areaADeleteOnly

 areaBCreateOnly
 areaBReadOnly
 areaBUpdateOnly
 areaBDeleteOnly

 areaCSuperUser
 areaCCreateOnly
 areaCReadOnly
 areaCUpdateOnly
 areaCDeleteOnly

As it's filtering out the parent and child, but not the children of children. The result I'm after (in this second example) is:

 |   name    |
 areaCSuperUser
 areaCCreateOnly
 areaCReadOnly
 areaCUpdateOnly
 areaCDeleteOnly

***Edit 2:

Specifically for Yii:

I've done some more reading into Yii, and CAuthManager has a number of helpful methods, including hasItemChild(), isAssigned(), getItemChildren(), ... and others, that could help with grabbing data like I need

score 1 · Accepted Answer · answered Sep 18 '12 at 11:00

1

This query may help you

SELECT DISTINCT `ai`.`name`
FROM `authitem` `ai`
LEFT JOIN `auithitemassignment` `aa`
ON `aa`.`itemname` = `ai`.`name` AND `aa`.`userid` = 1
WHERE  
`aa`.`itemname` IS NULL 
AND `ai`.`name` NOT IN (
    SELECT
    `aic`.`child` itemname
    FROM `authitemchild` `aic`
    JOIN `auithitemassignment` `aa`
    ON `aa`.`itemname` = `aic`.`parent`
    WHERE `aa`.`userid` = 1
)

answered Sep 18 '12 at 11:00

SuVeRa

2,784
2
20
27

Thank you, that does exactly what I needed to do, I fear there's not a way to get the results I need without using a sub select. Secondly, can you think of a way to filter out children of children too? I've updated the question with another example, I'm at even more of a loss here! – Stu Sep 18 '12 at 11:33
You are making the authitemchild table to store TREE kinda structure, it will definitely increase some complexity to read back the stored items. This node can help you how to retrieve tree kinda structure with single query. [link](http://stackoverflow.com/questions/169817/is-it-possible-to-query-a-tree-structure-table-in-mysql-in-a-single-query-to-an) – SuVeRa Sep 18 '12 at 11:42
Thanks, I'm familiar with the nested set model, and it probably would be better for this case but I'm trying to use Yiis [RBAC](http://www.yiiframework.com/doc/guide/1.1/en/topics.auth#role-based-access-control) feature which has to use this data structure, unless I'm gonna customise it heavily (which I think I might do!) thanks for the help anyhoo! :D – Stu Sep 18 '12 at 12:25

mySQL select from one table where does not exists in another, and is not a child in a third table

1 Answers1