Laravel Eloquent-查询数据透视表

用户名

在我的Laravel应用中,我有三个数据库表,分别称为用户,项目和角色。它们之间存在m:n关系,因此我还有一个名为project_user_role的数据透视表。数据透视表包含user_id,project_id和role_id列。请参见图像以获取MySQL Workbench的屏幕截图。

在此处输入图片说明

定义了我的用户,项目和角色模型,如下所示的belongsToMany关系:

//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",
        }
      ]
  },
]

可能吗?谢谢

克姆恩克尔

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()

至于结构,我认为您那里有点违反单一责任的情况。“用户”代表个人。但是您还使用它来表示项目的“参与者”的概念。我相信您需要一个新的Participant表,该表与User具有多对一关系,与Project具有一对一关系。然后,您的数据透视表只需在“参与者”和“角色”之间是多对多的,而将“用户”排除在外。

然后您的查询将如下所示:

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

这也将使您有机会添加一些数据,以描述诸如总的参与者状态,何时与该项目关联,何时离开该项目或他们的主管(parent_participant_id)之类的事情。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

如何使用多个外键查询数据透视表Laravel 5 Eloquent

Laravel Eloquent从多个表中检索数据

如何在Laravel 5中使用Eloquent更新数据透视表

Laravel Eloquent-使用数据透视表的ID作为另一个表中的外键

Laravel查询生成器与数据透视表

Laravel 5:如何使用Eloquent在数据透视表上进行联接查询?

在Laravel中用Eloquent查询联接表

Laravel的数据透视表+数据透视表

Laravel / Eloquent:数据透视表上的条件

Laravel如何查询与日期相等的数据透视表

Laravel Eloquent如何在数据透视表中选择和操作行?

我如何使用Eloquent&Laravel 5查询多态数据透视表

如何通过数据透视表上的关系计数进行排序-Laravel 4 / Eloquent

laravel雄辩的数据透视表查询“歧义列名”

Laravel通过数据透视表查询数据

Laravel Eloquent 获取与数据透视表中的值匹配的关系

查询生成器 - 数据透视表 laravel

Laravel Eloquent - 数据透视表

为什么我不能在 Laravel Eloquent 数据透视表中使用 ORDER BY?

Laravel Eloquent ManyToMany with Pivot,带有数据透视表中变量的辅助查询?

Laravel - 如何查询数据透视表上的关系

Laravel Eloquent 如何为数据透视表选择使用条件“where”

Laravel eloquent 构建查询

Eloquent 查询根据数据透视表添加新字段

Laravel Eloquent 查询与关系

查询 Eloquent laravel

如何在 Laravel eloquent 中查询数据透视表的计数

Laravel 查询生成器数据透视表连接

通过 laravel eloquent 查询四级表中的数据