Laravel Eloquent - 查询数据透视表 [英] Laravel Eloquent - querying pivot table

问题描述

在我的 Laravel 应用程序中，我有三个数据库表，分别称为用户、项目和角色.它们之间存在 m:n 关系，所以我还有名为 project_user_role 的数据透视表.数据透视表包含 user_id、project_id 和 role_id 列.请参阅 MySQL Workbench 的屏幕截图图像.

我的 User、Project 和 Role 模型被定义为属于这样的关系:

//用户模型示例公共功能项目(){return $this->belongsToMany('AppLibraryModelsProject', 'project_user_role')->withPivot(['user_id','role_id']);}

现在我可以轻松地获得这样的认证用户的项目:

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

这看起来像这样:

<预><代码>[{身份证":1，"name": "测试项目",用户 ID":1，"created_at": "2018-05-01 01:02:03","updated_at": "2018-05-01 01:02:03",枢轴":{用户 ID":2，project_id":17，角色 ID":1}},]

但我想将有关用户角色的信息注入"到响应中:

<预><代码>[{身份证":1，"name": "测试项目",用户 ID":1，"created_at": "2018-05-01 01:02:03","updated_at": "2018-05-01 01:02:03",枢轴":{用户 ID":2，project_id":17，角色 ID":1},角色: [{身份证":1，"name": "一些角色名称","display_name": "一些角色名称","description": "一些角色名称","created_at": "2018-05-01 01:02:03","updated_at": "2018-05-01 01:02:03",}]},]

有可能吗?谢谢

解决方案

您实际上是在要求对数据透视表进行预加载.问题是，数据透视表中的数据不是来自顶级模型类，因此没有任何关系方法供您参考.

您的数据库结构也有一点尴尬，因为您的数据透视表连接了三个表而不是两个.不过，在实际回答您的问题后，我会对此进行一些思考...

因此，您可以通过数据透视表从用户转到项目.您可以通过数据透视表从用户转到角色.但是您要寻找的是通过该数据透视表从项目转到角色.(即您想要的数据报显示项目数据是具有嵌套角色"的顶级.).只有当项目模型是您的入口点而不是您的用户时，才能这样做.

因此，首先向名为 roles 的项目模型添加多对多关系方法，然后像这样运行查询:

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

至于结构，我认为您在那里违反了单一职责.用户"代表个人.但是您也使用它来表示项目的参与者"的概念.我相信您需要一个新的 Participant 表，它与 User 是多对一的关系，与 Project 是一对一的关系.那么您的数据透视表只需要在 Participant 和 Role 之间进行多对多，而将 User 排除在外.

那么您的查询将如下所示:

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

这也让您有机会添加一些数据来描述整体参与者的状态、他们何时与该项目关联、他们何时离开该项目，或者他们的主管 (parent_participant_id) 可能是谁.

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('AppLibraryModelsProject', '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

解决方案

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 - 查询数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！