Laravel Eloquent - querying pivot table

Question

in my Laravel app I have three database tables called users, projects and roles. There is m:n relation between them so I have also pivot table called project_user_role. Pivot table contains user_id, project_id and role_id columns. See image for screenshot from MySQL Workbench.

My User, Project and Role models got defined belongsToMany relation like that:

//User model example
public function projects()
{
    return $this->belongsToMany('App\Library\Models\Project', 'project_user_role')->withPivot(['user_id','role_id']);
}

Now I can easily get projects of authenticated user like that:

$user = Auth::user();
$projects = $user->projects;

Response of this looks like that:

[
  {
      "id": 1,
      "name": "Test project",
      "user_id": 1,
      "created_at": "2018-05-01 01:02:03",
      "updated_at": "2018-05-01 01:02:03",
      "pivot": {
          "user_id": 2,
          "project_id": 17,
          "role_id": 1
      }
  },
]

but I would like to "inject" information about user role into response likat that:

[
  {
      "id": 1,
      "name": "Test project",
      "user_id": 1,
      "created_at": "2018-05-01 01:02:03",
      "updated_at": "2018-05-01 01:02:03",
      "pivot": {
          "user_id": 2,
          "project_id": 17,
          "role_id": 1
      },
      roles: [
        {
            "id": 1,
            "name": "some role name",
            "display_name": "Some role name",
            "description": "Some role name",
            "created_at": "2018-05-01 01:02:03",
            "updated_at": "2018-05-01 01:02:03",
        }
      ]
  },
]

Is it possible? Thanks

in user model define another 1 to many relation. user belongs to role and rols has many users. and eager load `$user->with('role', 'projects') — Farrukh Ayyaz

kmuenkel kmuenkel · Accepted Answer · 2018-06-01T14:59:37

You're essentially asking for an eager-load on a pivot table. The problem is, the data from the pivot table isn't coming from a top-level Model class, so there isn't anything in the way of a relationship method for you to reference.

There's a little awkwardness in your DB structure too, in that your pivot table is joining three tables instead of two. I'll get into some thoughts on that after actually answering your question though...

So, you can go from the User to the Project through the pivot table. And you can go from the User to the Role through your pivot table. But what you're looking for is to go from the Project to the Role through that pivot table. (i.e. your desired datagram shows the project data to be top-level with nested 'roles'.) . This can only be done if the Projects model is your entry point as opposed to your User.

So start by adding a many-to-many relation method to your Projects Model called roles, and run your query like this:

app(Projects::class)->with('roles')->wherePivot('user_id', Auth::user()->getKey())->get()

As for the structure, I think you have a little bit of a single-responsibility violation there. "User" represents an individual person. But you're also using it to represent the concept of a "Participant" of a project. I believe you need a new Participant table that has a many-to-one relationship with User, and a one-to-one relationship with Project. Then your pivot table need only be a many-to-many between Participant and Role, and leave User out of it.

Then your query would look like this:

Auth::user()->participants()->with(['project', 'roles'])->get()

This would also give you the opportunity to add some data describing things like what the overall participant.status is, when they became associated with that project, when they left that project, or who their supervisor (parent_participant_id) might be.

Laravel Eloquent - querying pivot table

2 Answers