LeeJ LeeJ - 3 months ago 9
MySQL Question

Laravel sql queries on 3rd relationship

I want to show all users who have a permission name of "Manager"

I have the following databases


users (many to many relationship with roles) | id: int

role_user (pivot table) | role_id: int user_id:int

roles (many to many relationship with users and has many relationship
with permissions ) | id:int

permissions (belongs to roles) | id:int name:string


Can someone please point me in right direction of how to compose the query, I'm using laravel 5.2 but happy for raw sql.

Answer

Raw sql will look like:

select * from users 
inner join role_user on users.id=role_user.user_id
inner join roles on role_user.role_id=roles.id
inner join permissions on roles.id=permissions.role_id
where permissions.name='Manager'
group by users.id