CODEWITHSUNDEEP
mysqlsqllaravellaravel-5eloquent
zyx4sdk
zyx4sdk

Reputation: 320

Get pivot field value for many to many relation in Laravel 5 app

I would like to get field value from pivot on many to many relationship in my Laravel 5.4 application.

I have following tables in my MySQL database:

products
 + id
 + name
 + ...


nutrients
 + id
 + name
 + ...

products_nutrients
 + product_id
 + nutrient_id
 + value

products and nutrients tables have its equivalent Eloquent model (Product and Nutrient) with properly working many to many relationship between them.

I need to list all products with values of certain nutrients. I know id of wanted nutrients, so I can set it in query. Result should look like collection/array following of element:

- product name
- value of nutrient id 1 for product
- value of nutrient id 2 for product
- value of nutrient id 3 for product

How can I figure it out using Eloquent query builder or in the worst case in raw SQL query? I want to avoid solution with foreach loop.

I am trying to achieve it but without success. I would be glad of any help. Thank you

Upvotes: 1

Views: 1702

Answers (2)

zyx4sdk
zyx4sdk

Reputation: 320

I managed to figure it out by raw SQL query:

SELECT product.name, protein.value as protein, fats.value as fats, carbo.value as carbo
FROM products product
LEFT JOIN products_nutrients protein ON (product.id = protein.products_id AND protein.nutrients_id = 1)
LEFT JOIN products_nutrients fats ON (product.id = fats.products_id AND fats.nutrients_id = 2)
LEFT JOIN products_nutrients carbo ON (product.id = carbo.products_id AND carbo.nutrients_id = 3)

Here is also Eloquent way to achieve the same as above:

$query = DB::table('products')
    ->join('products_nutrients as protein', function ($join) {
        $join->on('products.id', '=', 'protein.products_id');
        $join->where('protein.nutrients_id', '=', 1);
    })
    ->join('products_nutrients as fats', function ($join) {
        $join->on('products.id', '=', 'fats.products_id');
        $join->where('fats.nutrients_id', '=', 2);
    })
    ->join('products_nutrients as carbo', function ($join) {
        $join->on('products.id', '=', 'carbo.products_id');
        $join->where('carbo.nutrients_id', '=', 3);
    })
    ->select('name', 'protein.value as protein', 'fats.value as fats', 'carbo.value as carbo');

Upvotes: 0

Alexey Mezenin
Alexey Mezenin

Reputation: 163758

Use the ->pivot attribute. From docs:

As you have already learned, working with many-to-many relations requires the presence of an intermediate table. Eloquent provides some very helpful ways of interacting with this table. For example, let's assume our User object has many Role objects that it is related to. After accessing this relationship, we may access the intermediate table using the pivot attribute on the models:

$user = App\User::find(1);

foreach ($user->roles as $role) {
    echo $role->pivot->created_at;
}

https://laravel.com/docs/5.4/eloquent-relationships#many-to-many

Upvotes: 2

Related Questions