如何使用 Laravel eloquent 在数据透视表上执行额外的内部连接

how to perform additional inner join on pivot table with laravel eloquent

提问by roelleor

I have four tables:

我有四个表：

• foods: id, name
• users: id, name
• mealtypes: id, name
• food_user: id, food_id, user_id, mealtype_id

• 食物：ID，名称
• 用户：ID，姓名
• 膳食类型：ID，名称
• food_user：id、food_id、user_id、mealtype_id

foods and user have a many-to-many relationship mealtype has a one-to-one relationship with food_user

food 和 user 是多对多的关系，mealtype 和 food_user 是一对一的关系

In the end I would like to have an instance of a model with the following properties: food.name, users.name, mealtype.name

最后，我想要一个具有以下属性的模型实例：food.name、users.name、mealtype.name

normal sql would be:

正常的 sql 将是：

SELECT f.name, u.name, m.name FROM foods f
INNER JOIN food_user fu ON f.id = fu.food_id
INNER JOIN users u ON fu.id = u.id 
INNER JOIN mealtypes m ON fu.mealtype_id = m.id

Thanks for any help!

谢谢你的帮助！

采纳答案by Luis Dalmolin

You could do something like this with Eloquent and Query Builder, assuming you have a model named Food:

你可以用 Eloquent 和 Query Builder 做这样的事情，假设你有一个名为 Food 的模型：

$foods = Food::join('food_user', 'foods.id', '=', 'food_user.food_id')
             ->join('users', 'food_user.user_id', '=', 'users.id')
             ->join('mealtypes', 'food_user. mealtype_id', '=', 'mealtypes.id')
             ->get();

There's a good documentation about the query builder too: http://www.laravel.com/docs/queries

也有关于查询构建器的很好的文档：http: //www.laravel.com/docs/queries

回答by roelleor

To answer my own question a year later. I actually asked the wrong question. A pivot table is just a many-to-many relationship between two tables. If a table that represents this many-to-many relationship additionally relates to other tables it is not a pivot table. So in my case the table food_user should represent an eloquent entity on its own with the three relationships defined.

一年后回答我自己的问题。我其实问错了问题。数据透视表只是两个表之间的多对多关系。如果表示这种多对多关系的表还与其他表相关，则它不是数据透视表。所以在我的情况下，表 food_user 应该代表一个雄辩的实体，定义了三个关系。

Update, generic solution:

更新，通用解决方案：

I cannot give you my final solution in terms of Eloquent, since I haven't used it in ages (and didn't implement it there), so I do not have the knowledge anymore. In more general terms, one would need to create 4 models:

我无法在 Eloquent 方面给你我的最终解决方案，因为我已经很久没有使用它了（也没有在那里实现它），所以我不再有知识了。更笼统地说，需要创建 4 个模型：

1. Food
2. User
3. MealType
4. Meal (instead of FoodUser, don't like that name)

1. 食物
2. 用户
3. 膳食类型
4. Meal（而不是FoodUser，不喜欢那个名字）

Now, in the model Meal, you need to define your relations with the other models:

现在，在 Meal 模型中，您需要定义与其他模型的关系：

1. Meal.food has a many to one relation with Food
2. Meal.user has a many to one relation with User
3. Meal.mealType has a many to one relation with MealType
4. Some other properties such as Meal.calories (int) and Meal.date (DateTime)

1. Meal.food 与 Food 是多对一的关系
2. Meal.user 与 User 有多对一的关系
3. Meal.mealType 与 MealType 是多对一的关系
4. 其他一些属性，例如 Meal.calories (int) 和 Meal.date (DateTime)