LeeJ LeeJ - 1 year ago 58
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 Source

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