Query table using multiple foreign keys

So I’m just messing around with a NodeJS application, and I’m trying to implement a permission system using my database.
It’s my first time working with any kind of SQL environment, so please correct me if I’m doing something terribly wrong.
I have the following tables:

permissions:
  id,
  name

users:
  id,
  name

roles:
  id,
  name

user_permissions:
  id,
  user_id,
  permissions_id

role_permissions:
  id,
  role_id,
  permission_id

user_roles:
  id,
  user_id,
  role_id

So there are 2 ways a user can get a permission, either via a role assigned in user_roles, or directly via user_permissions.

I’ve already got the following query, which seems to work correctly.

SELECT permissions.name 
FROM permissions 
INNER JOIN role_permissions ON role_permissions.permission_id=permissions.id 
INNER JOIN user_roles ON user_roles.role_id=role_permissions.role_id 
AND user_roles.user_id=?

However, this only looks for permissions assigned via a role.
I can’t seem to get a query to work that queries the individual permissions correctly.

Whenever I try the following query

SELECT * FROM permissions INNER JOIN user_permissions ON user_permissions.user_id=?;

I get:

+----+-------------+----+---------+---------------+
| id | name        | id | user_id | permission_id |
+----+-------------+----+---------+---------------+
|  1 | permission1 |  1 |       3 |             5 |
|  2 | permission2 |  1 |       3 |             5 |
|  3 | permission3 |  1 |       3 |             5 |
|  4 | permission4 |  1 |       3 |             5 |
|  5 | permission5 |  1 |       3 |             5 |
+----+-------------+----+---------+---------------+

Here, I have permission5 assigned as an individual permission for user3, but because of the join it gets joined with every permission.

So I guess I don’t yet fully grasp what JOIN is doing. Maybe somebody could help me out here and explain what is going on?

Answer

You are missing a join with user_permissions. I think what you are after will be something like following

 select p.name
 from permissions p
 join user_permissions up on up.permissions_id = p.permissions_id 
 join user u on u.user_id = up.user_id
 where u.user_id = ?
 union 
 select p.name
 from permissions p 
 join role_permissions rp on rp.permission_id = p.permission_id
 join user_roles ur on ur.role_id = rp.role_id
 join user u on u.user_id = ur.user_id 
 where u.user_id = ?

Advertisement

Answer