0

I have a mysql table that drives a menu on my site. I have a column for each menu item that defines which user roles can view the menu item.

I store the "authorised" roles as a comma separated list

I am trying to build a query that allows me to supply a list of roles that a user is in and find the menu items that they are allowed to view. I tried using LIKE %ROLE1% %ROLE2%, etc... but this returns only those menu items that are in all those roles.

How can I find a user's roles and the corresponding menu items?

Toby Speight
  • 27,591
  • 48
  • 66
  • 103
James
  • 83
  • 7

1 Answers1

0

I agree to what everybody would tell you, that your database needs normalization and more specifically, to consider First Normal Form. You should create a MENU_ITEM_ROLE table that would be used as bridge table between ROLE and MENU_ITEM. It would hold multiple records with the primary key of the MENU_ITEM entity and with the respective ROLE needed to access it.

However, given the situation you have, you said you tried: LIKE %ROLE1% %ROLE2% e.t.c. and it returned only menu items that are available to all the roles. I assume that this is probably because you used AND operator between the different LIKE expressions. The appropriate operator I would say is OR in this case, and only the menu items that have at least one of the ORed roles will be retrieved.

You should consider though that this solution and trying to solve this problem with string manipulation would cause more troubles if your role names have similar values. E.g. having a menu item A available on role 'ROLE_ADMIN', and another menu item B available on role 'ROLE_ADMIN_USERS', then a user having the role 'ROLE_ADMIN' will be able to see both menu items A and B.

The most reliable solution, given the situation, is to use functions that tokenize the roles field of MENU_ITEM table, and then search for matches. In this direction I would use something like FIND_IN_SET.

E.g.

select * from menu_item where find_in_set('ROLE1', roles)>0

Reference also here.

Community
  • 1
  • 1
sanastasiadis
  • 1,182
  • 1
  • 15
  • 23