Hello dear SO community,

I have been struggling with this for some time now and since time is running out I turn to you.

I have these tables:

USERS:
user_id (PK)
user_name

PROJECTS:
project_id (PK)
project_name
user_id (FK) - referring to the creator of the project

Users and projects are in M:N relationship, which is captured in table PERMISSIONS.

PERMISSIONS:
project_id (FK)
user_id (FK)

Permissions row holds a access rule for single user and single project. If a pair for given user and project does not exist in the table, that user cannot access that project.

I am trying to query all projects that current user should have access to, meaning those that he created and those that he is associated with in the PERMISSIONS table but with every project I want the user_name of its creator. I supply current user ID to a prepared statement in PHP, which works fine, but I cannot seem to get the query right. I have been able

I was able to to get projects that were created or accessible by certain user, but I cannot figure out how to join USERS, to get the user_name of user_id in projects (username of the project creator) that I query.

I am querying something along the lines of:

SELECT projects.name as name, projects.project_id as project_id, projects.user_id  as user_id, users.name as user_name
FROM projects JOIN permissions USING (project_id) JOIN users USING (user_id) WHERE permissions.user_id=:user_id OR 
projects.user_id=:user_id ORDER BY name

CodePudding user response：

The issue in your query is that the user_id field should be both matched:

• between users and permissions, to catch projects the user is allowed to work on
• between users and projects, to catch projects the user has created

One way of solving this issue is to have a UNION operation between these two result sets. In order to make the joins more efficient, it's better to move the filtering operation (finding the specific user) before the join operation, that's the reason why I've moved it into a subquery (under the shape of common table expression - cte).

WITH user_details AS(
    SELECT user_id, 
           user_name 
    FROM users
    WHERE user_id = < user_id_of_your_choice >
)
SELECT *
FROM       user_details 
INNER JOIN projects     
        ON user_details.user_id = projects.user_id 

UNION 

SELECT user_details.*,
       permissions.*
FROM       user_details 
INNER JOIN permissions 
        ON user_details.user_id = permissions.user_id
INNER JOIN projects
        ON permissions.project_id = projects.project_id

You can try it in a sample environment here.

CodePudding user response：

Join twice: once to get all projects this user is permitted or created, and the second time specifically to get the user who created the project:

SELECT p.name as name, p.project_id as project_id, p.user_id  as user_id, uu.name as user_name, uc.name as creator_name
FROM projects P 
   JOIN permissions Pu
   On p.project_id=p.project_id
   
   JOIN users uu
   On uu.user_id=pu.user_id

   Join
   Permissions pc
   On pc.project_id=p.project_id
   And --- add criteria here to distinguish creator

   Join
   Users uc
   On uc.user_id=PC.user_id

WHERE pu.user_id=:user_id OR 
pu.user_id=:user_id 

CodePudding user response：

There are three possibilities:

1. Have a join on projects with or, to fetch both creators and those with permission. A join with or is generally not advisable because it leads to table scans. Distinct is needed

   select distinct u.user_name, p.project_name
   from users u
   left join permissions pm on pm.user_id = u.user_id
   left join projects p on p.user_id = u.user_id or p.project_id = pm.project_id
   where u.user_id = 2;
   

2. Cross join projects and have the 'or's in the where clause. Need distinct.

    select distinct u.user_name, p.project_name
    from users u
    cross join projects p
    left join permissions pm on pm.user_id = u.user_id
    where u.user_id = 2 and ( p.project_id = pm.project_id or  p.user_id = u.user_id );
   

3. Use union, one select for creators and one for those with permission. Distinct is not needed because union does distinct

      select u.user_name, p.project_name
      from users u
      inner join projects p on p.user_id = u.user_id
      where u.user_id = 2
    union 
      select u.user_name, p.project_name
      from users u
      inner join permissions pm on pm.user_id = u.user_id
      inner join projects p on p.project_id = pm.project_id
      where u.user_id = 2
   

online editor here: https://onecompiler.com/mysql/3y64ukxtp