Donny van V Donny van V - 1 year ago 70
MySQL Question

Render menu based on user, roles and methods

I have a issue with rendering a menu that uses roles and users. Im using Laravel 5.2 (just for info and maybe a easy solution :) )
What im trying to achieve is to get the current user, with the roles which has actions. The problem is: 2 different roles can contain the
same method and i dont want to get them twice but just one time. How can i achieve this easily?

Can someone help me out please?

My table structure:

- users
-- id
-- email

- roles
-- role_id
-- title

- methods
-- id
-- title

- users_roles
-- user_id
-- role_id

- methods_roles
-- role_id
-- method_id

Answer Source

This query should give you all the methods (no duplicates) for the user with $your_user_id:

select * 
from methods m
inner join methods_roles mr on = mr.method_id
inner join user_roles ur on ur.role_id = mr.role_id
where ur.user_id = $your_user_id
group by