Eloquent query with subquery on pivot

I have two eloquent models: User, Project with a many-to-many relationship between them. In User model:

public function projects()
{
   return $this->belongsToMany(Project::class)->withTimestamps();
}

and in Project model:

public function users() 
{
   return $this->belongsToMany(User::class)->withTimestamps();
}

The database tables are user, project and the pivot table is project_user. The table fields are as follows: user: [id, name], project: [id, data], project_user: [user_id, project_id].

I need to implement a query that given a user returns all the users that are participating in all his projects and I cannot figure out how to do this with eloquent. Actually the tables that are needed in that query are user and project_user. I guess a possible sql query could be something like this:

SELECT id, name FROM user WHERE id IN (SELECT user_id FROM project_user WHERE project_id IN (SELECT project_id FROM project_user WHERE user_id = 8))

In Eloquent I do not even know how to approach this. I am new to it. For instance do I somehow need the whole relationship so as to use wherePivot() or try subqueries in where() clauses in the User model? Any help or hints would be much appreciated.

ahmeddabak

6 years ago

Best Answer

Level 47

there are many ways you can solve this, an easy way would be to query the relation like this

$user_projects = User::find(1)->projects->pluck('id'); //get ids of all user projects

$users = User::whereHas('projects',function($query) use($user_projects ) {
    $query->whereIn('project_id', $user_projects );
})->get();

read more about Querying Relationship Existence

1 like

csm2pk

6 years ago

Level 1

So the key was to use whereHas() and whereIn(). A simple and efficient solution! Thank you!

Please or to participate in this conversation.