MySQL - Too many joins! Only display records if user has permission-CodePudding

I'm trying to make a MySQL query for a small project management CMS I'm developing with PHP. I have four tables; projects, projectInfo, projectUsers & accounts. I can use JOINs to get a list of all projects for all users, along with their most recent project name and description. However, I would like to see all projects that a specific user is contributing on (from table projectUsers). Each project has a creatorId in the projects table, which I can query by adding " AND a.creatorId = 1" to the end of the query below. However, I also need another query that will display all the same project information if the users' foreign key exists in projectUsers table.

Query to display all projects with relevant information:

SELECT p.projectId, p.projectInfoId, p.userId, a.creatorId, p.updatedTimestamp, p.projectName, a.timestamp, a.projectType, accounts.firstname, accounts.lastname 
FROM projects AS a 
JOIN projectInfo AS p ON p.projectInfoId = ( SELECT pi.projectInfoId FROM projectInfo AS pi WHERE pi.projectId = a.id ORDER BY pi.updatedTimestamp DESC LIMIT 1 ) 
INNER JOIN accounts ON a.creatorId=accounts.id 
WHERE a.deleted = 0

Gives me the following results:

| projectId | userId | creatorId | updateTimestamp     | projectName              | timestamp           | projectType | firstname | lastname |
| --------- | ------ | --------- | ------------------- | ------------------------ | ------------------- | ----------- | --------- | ---------|
| 155       | 1      | 1         | 2022-07-25 10:10:28 | Chicken Nugget Dispenser | 2022-07-25 10:04:01 | Production  | Ryan      | Admin    |
| 156       | 1      | 1         | 2022-07-25 10:05:44 | Nugget Juice Dispenser   | 2022-07-25 10:05:44 | Design      | Ryan      | Admin    |

projects table

| id       | timestamp           | projectType | creatorId | deleted |
| -------- | ------------------- | ----------- | --------- | ------- |
| 155      | 2022-07-25 10:04:01 | Production  | 1         | 0       |
| 156      | 2022-07-25 10:05:44 | Design      | 1         | 0       |

projectInfo table

| projectInfoId | projectId | userId | updatedTimestamp    | projectName              | projectDescription                         |
| ------------- | --------- | ------ | ------------------- | ------------------------ | ------------------------------------------ |
| 172           | 155       | 1      | 2022-07-25 10:10:28 | Chicken Nugget Dispenser | Dispenses chicken nuggets, idk... Update 2 |
| 171           | 155       | 1      | 2022-07-25 10:10:24 | Chicken Nugget Dispenser | Dispenses chicken nuggets, idk... Update 1 |
| 170           | 156       | 1      | 2022-07-25 10:05:44 | Nugget Juice Dispenser   | Dispenses chicken nugget juice.            |
| 169           | 155       | 1      | 2022-07-25 10:04:01 | Chicken Nugget Dispenser | Dispenses chicken nuggets, idk...          |

accounts table

| id | username | password | email | role    | firstname | lastname |
| -- | -------- | -------- | ----- | ------- | --------- | -------- |
| 1  | admin    | ******** | ***** | Admin   | Ryan      | Admin    |
| 35 | manager  | ******** | ***** | Manager | Ryan      | Manager  |
| 36 | user     | ******** | ***** | User    | Ryan      | User     |

projecUsers table

| id  | addedTimestamp      | projectId | userId | addedBy | 
| --- | ------------------- | --------  | ------ | ------- |
| 213 | 2022-07-25 10:04:01 | 155       | 35     | 1       | 
| 214 | 2022-07-25 10:04:01 | 155       | 36     | 1       |

I have attempted to solve this by adding "INNER JOIN projectUsers ON 1=projectUsers.userId", but I end up with no results. I have also tried subquerying projectUsers, as well as a few attempts at IF EXISTS, to no avail. Eventually, my PHP script would replace the foreign key "1" with the userId from the database, using the $_SESSION variable, so the user can view all projects that they are assigned from the projectUsers table.

Any help would be appreciated!

Thanks, Ryan.

CodePudding user response：

You want many rows from 4 tables, there mus be enough joins for that.

you choice for aliases, are very bad, you must see at once which table is the source

SELECT
 pu.`projectId`,pi.`projectInfoId`, pu.`userId`,pr.`creatorId`
 ,pi.`updatedTimestamp`,
 pi.`projectName`,pr.`timestamp`, pr.`projectType`
 ,ac.`firstname`, ac.`lastname`
FROM
projecUsers pu 
JOIN projects pr ON pr.`id` = pu.`projectId`
JOIN projectInfo pi ON pr.`id` = pi.`projectId`
JOIN accounts ac ON pr.`creatorId` = ac.`id`
WHERE pu.`userId` = 35 AND pr.`deleted` = 0

projectId | projectInfoId | userId | creatorId | updatedTimestamp    | projectName              | timestamp           | projectType | firstname | lastname
:-------- | ------------: | :----- | --------: | :------------------ | :----------------------- | :------------------ | :---------- | :-------- | :-------
155       |           172 | 35     |         1 | 2022-07-25 10:10:28 | Chicken Nugget Dispenser | 2022-07-25 10:04:01 | Production  | Ryan      | Admin   
155       |           171 | 35     |         1 | 2022-07-25 10:10:24 | Chicken Nugget Dispenser | 2022-07-25 10:04:01 | Production  | Ryan      | Admin   
155       |           169 | 35     |         1 | 2022-07-25 10:04:01 | Chicken Nugget Dispenser | 2022-07-25 10:04:01 | Production  | Ryan      | Admin

db<>fiddle here

CodePudding user response：

Provided permissions are projectUsers(projectId,UserId)

    SELECT p.projectId, p.projectInfoId, p.userId, a.creatorId, p.updatedTimestamp, p.projectName, a.timestamp, a.projectType, accounts.firstname, accounts.lastname 
    FROM projects AS a 
    JOIN projectInfo AS p ON p.projectInfoId = ( SELECT pi.projectInfoId FROM projectInfo AS pi WHERE pi.projectId = a.id ORDER BY pi.updatedTimestamp DESC LIMIT 1 )
    INNER JOIN projectUsers pu on pu.projectId = a.id
    INNER JOIN accounts ON accounts.Id = pu.UserId 
       and accounts.id = ?
    WHERE a.deleted = 0