問題描述

在我的 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;

這看起來像這樣:

但我想將有關用戶角色的信息注入"到響應中:

有可能嗎?謝謝

您實際上是在要求對數據透視表進行預加載.問題是，數據透視表中的數據不是來自頂級模型類，因此沒有任何關系方法供您參考.

您的數據庫結構也有一點尷尬，因為您的數據透視表連接了三個表而不是兩個.不過，在實際回答您的問題后，我會對此進行一些思考...

因此，您可以通過數據透視表從用戶轉到項目.您可以通過數據透視表從用戶轉到角色.但是您要尋找的是通過該數據透視表從項目轉到角色.(即您想要的數據報顯示項目數據是具有嵌套角色"的頂級.).只有當項目模型是您的入口點而不是您的用戶時，才能這樣做.

因此，首先向名為 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 - 查詢數據透視表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！