I am creating a user permission system. I will have my user permissions set up off of "objects" and not pages. For example, I have an
EVENTS section. Within that section, there are
FEES, etc. I want to be able to handle permissions down to the child levels.
Here is my permission_objects table:
| id | object | parent_id | +----+--------+-----------+ | 1 | EVENTS | NULL | | 2 | FORMS | 1 | | 3 | FEES | 1 |
Here is my current query:
SELECT `up`.* FROM `permission_objects` `po` INNER JOIN `user_permissions` `up` ON `up`.`object`=`po`.`id` AND (`up`.`user_group`=2 OR `up`.`user_id`=17) WHERE `po`.`object`='EVENTS' OR `po`.`parent_id`=( SELECT `id` FROM `permission_objects` WHERE `object`='EVENTS' )
I really want to avoid doing a subquery. I thought that instead of having a
parent_id I could have
parent_object and have that contain the parent's object value. i.e.
| 2 | FORMS | EVENTS |. That way I could change my
WHERE clause to:
WHERE `po`.`object`='EVENTS' OR `po`.`parent_object`='EVENTS'
However I feel that keeping it as
parent_id is a better approach. Is there a way to keep that but not have to have a subquery in my sql?