Check if value exists in MySQL table and then select [Laravel 5]

Question

Here is my pivot table project_group:

+-----+----------+------------+----------+---------+
| ids | group_id | project_id | admin_id | user_id |
+-----+----------+------------+----------+---------+
|   4 |      115 |          1 |        1 | [3,4,5] |
|   5 |      115 |          2 |        1 | [5,2,1] |
|   6 |      115 |          3 |        1 | [1,3,6] |

This table represent group linked to the projects....user_id is which users can see projects/group... Is there any way to display correct projects/group only to the users defined in user_id?

Also content in user_id field can be changed....

Basically resuting from some bad database design. Dont store data like that — RiggsFolly
– RiggsFolly, Commented May 27, 2015 at 13:26
you should have another table with 3 columns. id(auto_incremented), project_id, user_id. Then you do joins, and selects with the results. — Dimi
– Dimi, Commented May 27, 2015 at 13:30

Community · Accepted Answer · 2017-05-23 11:58:11Z

1

The best way to handle this would be to first normalize your database. Storing comma separated lists in a cell is allowed, but generally bad practice, as explained in this question.

If you can have multiple users per project, you should have a linking table with a column for project and a column for user, like this:

project_users:
| project_id | user_id |

and you can make (project_id, user_id) a composite primary key.

That way, you can select the users for a project (say, project 1) like this:

SELECT user_id
FROM project_users
WHERE project_id = 1;

Once you have these, you can display the project data only to users whose id is returned in the above list.

I have built an SQL Fiddle that helps demonstrate this visually, if it helps.

It is good to note that this proper normalization gives the opportunity to a lot of useful data as well, as it becomes easier to search for users by project, but also you can search for project information based on a user.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered May 27, 2015 at 13:43

AdamMc331

16.7k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Vladimir Djukic Over a year ago

Thanks! After I change database how can I select projects/group with Laravel 5 for user that is currently logged?

Vladimir Djukic Over a year ago

Is that possible because I have 2 pivot table project_user, project_group?

AdamMc331 Over a year ago

@VladimirDjukic I'm not sure what you mean?

Vladimir Djukic Over a year ago

For example user with id 3 is logged into the system and he should get on the home page name of project with id 1 and 3 (look from table above).... How to create query for that with Laravel 5?

AdamMc331 Over a year ago

I don't have a lot of experience with Laravel, but you can get projects 1 and 3 by using SELECT * FROM project_users WHERE user_id = 3. Hope that pushes you in the right direction!

|

Collectives™ on Stack Overflow

Check if value exists in MySQL table and then select [Laravel 5]

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related