How to make an additional inner join on a pivot table with an eloquent caravan

Question

I have four tables:

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

products and user are many-to-many foodtype has a one-to-one relationship with food_user

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

normal sql will be:

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!

Answer 1

You can do something similar with Eloquent and Query Builder, assuming you have a model called 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 is also good documentation about the query builder there: http://www.laravel.com/docs/queries

Answer 2

To answer my question in a year. I really asked the wrong question. A pivot table is simply a many-to-many relationship between two tables. If the table that represents this many-to-many relationship additionally refers to other tables, this is not a pivot table. Therefore, in my case, the food_user table should be an eloquent entity in itself with three defined relationships.