Laravel Eloquent-查询数据透视表 [英] Laravel Eloquent - querying pivot table
问题描述
在我的Laravel应用程序中，我有三个数据库表，分别称为用户，项目和角色.它们之间存在m:n关系，因此我还有一个名为project_user_role的数据透视表.数据透视表包含user_id，project_id和role_id列.请参阅图像以获取MySQL Workbench的屏幕截图.
我的用户，项目和角色模型已定义为属toMany关系，
//User model example
public function projects()
{
return $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",
"pivot": {
"user_id": 2,
"project_id": 17,
"role_id": 1
}
},
]
但是我想将有关用户角色的信息注入"到响应中:
[
{
"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",
}
]
},
]
有可能吗?谢谢
本质上，您是在数据透视表上请求快速加载.问题在于，数据透视表中的数据不是来自顶级Model类，因此没有任何关系方法可供您参考.
您的数据库结构也有些尴尬，因为您的数据透视表将三个表而不是两个表连接在一起.在实际上回答了您的问题之后，我会对此进行一些思考...
因此，您可以通过数据透视表从用户转到项目.您可以通过数据透视表从用户"转到角色".但是，您要寻找的是通过该数据透视表从项目转到角色. (即，您所需的数据报将项目数据显示为具有嵌套角色"的顶级数据.).仅当Projects模型是您的入口点，而不是您的用户时，才可以这样做.
因此，首先应在项目模型中添加一个名为roles的多对多关系方法，然后像这样运行查询:
app(Projects::class)->with('roles')->wherePivot('user_id', Auth::user()->getKey())->get()
关于结构，我认为您在那儿有点违反单一职责的情况. 用户"代表个人.但是您还使用它来表示项目的参与者"的概念.我相信您需要一个新的Participant表，该表与User具有多对一关系，与Project具有一对一关系.然后，您的数据透视表只需在参与者"和角色"之间是多对多的，而将用户"排除在外.
然后您的查询将如下所示:
Auth::user()->participants()->with(['project', 'roles'])->get()
这也将使您有机会添加一些数据，以描述诸如总体参与者的状态，何时与该项目关联，何时离开该项目，或他们的主管(parent_participant_id)之类的事物. /p>
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
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屋！
