dongzongxun8491
2018-06-01 14:30
浏览 45
已采纳

Laravel Eloquent - 查询数据透视表

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.

enter image description here

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

图片转代码服务由CSDN问答提供 功能建议

在我的Laravel应用程序中,

我有三个数据库表,称为用户,项目和角色。 它们之间有m:n关系,所以我还有一个名为project_user_role的数据透视表。 数据透视表包含user_id,project_id和role_id列。 请参阅图片,了解MySQL Workbench的截图。

我的用户,项目和角色模型已获得 像这样定义belongsToMany关系:

  //用户模型示例
公共函数项目()
 {
返回$ this-> belongsToMany('App \ Library \ Models  \ Project','project_user_role') - > withPivot(['user_id','role_id']); 
} 
   
 
 

现在我可以轻松获得项目 经过身份验证的用户:

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

响应如下:

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

但我想将有关用户角色的信息“注入”到响应中: < pre> [ { “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 }, 角色:[ { ”id“:1, ”名称“:”某个角色名称“, ”display_name“:”某个角色名称“, “description”:“some role name”, “created_at”:“2018-05-01 01:02:03”, “updated_at”:“2018-05-01 01:02:03”, } ] }, ]

有可能吗? 谢谢

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dtbonklcs575884485 2018-06-01 14:59
    已采纳

    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.

    点赞 评论
  • douci1677 2018-06-01 14:42

    I guess yes you could load roles for your projects collection, Define same mapping in project model

    //Project model
    public function roles()
    {
        return $this->belongsToMany('App\Library\Models\Role', 'project_user_role', 'project_id');
    }
    

    And use eager loading in your user model mapping defined for projects

    public function projects()
    {
        return $this->belongsToMany('App\Library\Models\Project', 'project_user_role')
                    ->with('roles')
                    ->withPivot(['user_id','role_id']);
    }
    
    点赞 评论

相关推荐 更多相似问题