lundi 27 juin 2016

Query data based on pivot table

I have a database design which essentially takes the following form (I have removed a lot of fields as they are not really needed for demonstration purposes). I have a users table

users
+----+---------------+-----------------+
| id | name          | email           | 
+----+---------------+-----------------+
| 1  | ProjectA      | Something       |    
+----+---------------+-----------------+

A user can have many projects

projects
+----+---------------+-----------------+-----------------+
| id | name          | description     | user_id         |
+----+---------------+-----------------+-----------------+
| 1  | ProjectA      | Something       | 1               | 
+----+---------------+-----------------+-----------------+

So that is straight forward enough and very easy to query. If I want all projects for the logged in user I can do

$loggedInUser = Auth::user()->getFirstName() . ' '  . Auth::user()->getLastName();
$loggedInUserId = User::where('userName', '=', $loggedInUser)->first();

$projectss = Project::all()->where('user_id', $loggedInUserId);

This is where things get a little more tricky. I have a pivot table for a users groups. It is essentially this

users_user_groups
+----+---------------+-----------------+
| id | user_id       | group_id        | 
+----+---------------+-----------------+
| 1  | 1             | 2               |    
+----+---------------+-----------------+

I then have a user_groups table

user_groups
+----+---------------+
| id | group_name    |             
+----+---------------+
| 1  | Group A       | 
+----+---------------+

If I want to find what groups a user is a part of I can do

$userGroups = Auth::user()->getGroups();

My question is this. Above, I demonstrate how I can get all projects for a user. Now I know there is a user_group called Group A. What I essentially want to do is get all projects where the user is apart of Group A. So if 5 users create a project and they are all in Group A, then I should be returned 5 projects.

How would I go about doing something like this?

Thanks



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire