0

I have a table that has all role details as below.

roles:

   id   name 
   1    level 1   
   2    level 2
   3    level 3
   4    level 4

Another table has user role details as below.

User_roles:

user_id    role_id    leader_id        company_id
100              1          102          1000
100              4                       1001
101              4                       1001
101              1           102         1000 
102              2           105         1000  
102              3           101         1001    
103              2           102         1001

A user can have unlimited roles. Each role also has a company and leader associated with it although leader is optional as they can be the highest. There are 8 levels of users but for some companies the maximum level they may use could be 3 or 4, so it varies.

As above, user 101 is under user 102 for one role and company. Then for another role user 102 is under user 101 for different company. A level 2 user can only see level 1 users which are assigned under him.

A level 4 user, however, can only see those level 1 users who belong to in the teams under him. If you think of it as a tree like structure, they can only see the users on their branches. Now, if I pass a user_id(103), how can I query whether he can be accessed by current logged in user id(101). As you can see, both 100 and 101 are level 4 on the same company. However, only 101 can see 103.

how can query that in MySQL? Or will it be better to restructure the tables for easier query.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
James
  • 308
  • 4
  • 15
  • As you've probably discovered, MySQL does not have native support for recursion. Choices include: using a sproc; handling the recursion at the application level; joining the table to itself as often as could be required; adjusting your data model to something like the nested set model. There may be other solutions but I suspect that all of the above are widely discussed elsewhere. – Strawberry Jun 26 '14 at 15:27
  • there are a few posts on here about mysql tree structure... there are a few attempts made but there is no actual support for recursion in mysql. i would look into rearranging the table structure with multiple tables (one for each level) and a PK - FK set up.. should make this task much simpler – John Ruddell Jun 26 '14 at 15:29
  • If I retrieve all child, grandchild etc records for the leader id(101) according to the answer mentioned http://stackoverflow.com/questions/11497202/get-all-child-grandchild-etc-nodes-under-parent-using-php-with-mysql-query-resu I would have a list of user_ids. And then if I see if the user_id(103) exists in the user_ids, will it not do that job? It's more like on php level rather than MySQL. Will need to try it out. – James Jun 26 '14 at 15:57

0 Answers0