Eager Loading: Use `with` on pivot with eloquent relationship

Question

There are 4 tables:

• bundles: id, name
• products: id, name
• prices: id, name
• bundle_product: id, bundle_id, product_id, price_id

There are 3 models:

• Bundle
• Product
• Price

A Product has a Price when in a Bundle. I want to have all bundles with their associated products and associated prices. I can get all bundles with their product and the price id:

// I created a Bundle Model with a products method
class Bundle extends Model
{
    public function products()
    {
        return $this->belongsToMany(Product::class)->withPivot('price_id');
    }
}

// Then I call this in a controller
$all_bundles = Bundle::with('products')->get();

// Then I can get the price Id of the first product of the first bundle
$price_id = Bundle::with('products')->first()
    ->products()->first()
    ->pivot->price_id;

But I dont want the price id, I want the price Model. Is there any way to preload the price from the pivot (with Eager Loading)?

Solution 1

One solution could be adding a BundleProduct model for the pivot. Then link the BundleProduct object to Bundle model:

class Bundle extends Model
{
    public function bundleProducts()
    {
        return $this->hasMany(BundleProduct::class, 'bundle_id', 'id');
    }
}

To get all your bundles with their associated products and prices in this bundle, just do:

Bundle::with('bundleProducts.product', 'bundleProducts.price')->get();

That worked for me, hope it could help someone else.

Solution 2

The current accepted answer deviates from the original data structure. I created a package which can help you achieve what you want and also it maintains the original data structure. Please read my medium story here: https://medium.com/@ajcastro29/laravel-eloquent-eager-load-pivot-relations-dba579f3fd3a

First, create your custom pivot model and define relations on pivot model, in your case:

use Illuminate\Database\Eloquent\Relations\Pivot;

class BundleProduct extends Pivot
{
    public function price()
    {
        return $this->belongsTo(Price::class);
    }
}

Then use the pivot model in the relation:

class Bundle extends Model
{
    public function products()
    {
        return $this->belongsToMany(Product::class)
        ->withPivot('price_id') // this is needed to query the relation `price`
        ->using(BundleProduct::class);
    }
}

Make sure you use the trait AjCastro\EagerLoadPivotRelations\EagerLoadPivotTrait in the Product model because it is the related model in belongsToMany relation. This let us enable eager loading the pivot relations.

use AjCastro\EagerLoadPivotRelations\EagerLoadPivotTrait;

class Product extends Model 
{
  use EagerLoadPivotTrait;
}

Then eager load it like this:

$bundle = Bundle::with('products.pivot.price')->first();
$price = $bundle->products->first()->pivot->price;

Solution 3

I think you'll struggle for a way to load the price model with eager loading alone, unless there is a way to pass a parent (the product, in this case) into a closure - which I'm pretty sure there isn't.

$all_bundles = Bundle::with(['products.prices' => function($query) use (PARENT) {
    $query->where('id', PARENT->pivot->price_id);
}])->get();

You could do this to load only the relevant price model, rather than all price models, after the initial query though:

$all_bundles = Bundle::with('products')->get();

foreach($all_bundles as $bundle) {
    foreach($bundle->products as $product) {
        $product->load(['prices' => function($query) use ($product) {
            $query->where('id', $product->pivot->price_id);
        }]);
    }
}

Solution 4

Documentation: Eloquent: Relationships: Many To Many

By default, only the model keys will be present on the pivot object. If your pivot table contains extra attributes, you must specify them when defining the relationship:

return $this->belongsToMany('App\Role')->withPivot('column1', 'column2');

That means that the withPivot() method only adds missing fields to the pivot object. To retrieve the actual Price model you will have to set up another relationship. It would look something like this:

/**
 * Class Bundle
 * 
 * Bundle model with the products relationship
 */
class Bundle extends Model
{
    public function products()
    {
        return $this->belongsToMany(Product::class);
    }
}

/**
 * Class Product
 * 
 * Product model with the prices relationship
 */
class Product extends Model
{
    public function prices()
    {
        return $this->belongsToMany(Price::class);
    }
}

/**
 * Class Price
 * 
 * Simple model, end result
 */
class Price extends Model
{
    // Model definition...
}

This is basic and of course not the complete code, only the functions necessary for the answer.

After this all you need to do to retrieve relationships is the following:

// You can query a child relationship using the dot (.). Prices collection will be accessible within each member of the products collection 
$all_bundles = Bundle::with('products.prices')->get();

$price_id = $all_bundles->first()
    ->products->first()
    ->prices->first()->id;

View more solutions

Comments

[VijayRana]

I was also wondering for this solution. I landed over here and solved my problem. Is it standard way to do this? @rap-2-h

[Samir Mammadhasanov]

Main question is about Pivot models. Eager loading which mentioned you is about HasMany relation, not ManyToMany. -1