Has many through many-to-many

Question

I have the following table layout:

deals:
- id
- price

products:
- id
- name

deal_product:
- id
- deal_id
- product_id

metrics:
- id
- name

metric_product:
- id
- metric_id
- product_id
- value

products and metrics have a many-to-many relationship with a pivot column of value.

deals and products also have a many-to-many relationship.

I can get metrics for a product with $product->metrics, but I want to be able to get all metrics for all products related to a deal, so I could do something like this: $deal->metrics.

I currently have the following in my Deal model:

public function metrics()
{
    $products = $this->products()->pluck('products.id')->all();

    return Metric::whereHas('products', function (Builder $query) use ($products) {
        $query->whereIn('products.id', $products);
    });
}

But this doesn't return a relationship, so I cannot eager load it or get related models from it.

It needs to be in relationship format, because they need to be eager loaded for my use case.

Thanks for your help!

Solution 1

If you want to have a custom relation, you can create your own extends to Relation abstract class. For example: BelongsToManyThought.

But if you don't want to implement a Relation, I think that it can fulfill your needs :

In App\Deal.php, you can combine the solution of @thomas-van-der-veen

public function metrics()
{
    return Metric

    ::join('metric_product', 'metric.id', '=', 'metric_product.metric_id')

    ->join('products', 'metric_product.product_id', '=', 'products.id')

    ->join('deal_product', 'products.id', '=', 'deal_product.product_id')

    ->join('deals', 'deal_product.deal_id', '=', 'deal.id')

    ->where('deal.id', $this->id);

}


// you can access to $deal->metrics and use eager loading
public function getMetricsAttribute()
{
    if (!$this->relationLoaded('products') || !$this->products->first()->relationLoaded('metrics')) {
        $this->load('products.metrics');
    }

    return collect($this->products->lists('metrics'))->collapse()->unique();
}

You can refer to this post to see how you can simply use nested relations.

This solution can do the trick for querying relation with method and access to metrics attribute.

Solution 2

There is a Laravel 5.5 composer package that can perform multi-level relationships (deep)

Package: https://github.com/staudenmeir/eloquent-has-many-deep

Example:

User → belongs to many → Role → belongs to many → Permission

class User extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function permissions()
    {
        return $this->hasManyDeep(
            'App\Permission',
            ['role_user', 'App\Role', 'permission_role'], // Pivot tables/models starting from the Parent, which is the User
        );
    }
}

Example if foreign keys need to be defined:

https://github.com/staudenmeir/eloquent-has-many-deep/issues/7#issuecomment-431477943

Solution 3

I don't have enough rep to post a comment so I'll post an answer instead.

A few days ago I posted a similar question and was able to answer it myself. I think the solution also works for this question.

By joining the tables manually and add a where clause you are able to retrieve the desired Metrics.

Possible solution:

// Deal.php
public function metrics()
{
    return Metric

        ::join('metric_product', 'metric.id', '=', 'metric_product.metric_id')

        ->join('products', 'metric_product.product_id', '=', 'products.id')

        ->join('deal_product', 'products.id', '=', 'deal_product.product_id')

        ->join('deals', 'deal_product.deal_id', '=', 'deal.id')

        ->where('deal.id', $this->id);

}

// How to retrieve metrics
$deal->metrics()->get();

// You can still use other functions like (what I needed) pagination
$deal->metrics()->paginate(24);

// Or add more where clauses
$deal->metrics()->where(blablabla)->get();

Hope this helps :)

Edit after @ntzm comment

If you only need some properties of the metrics table and none of the Model functionalities you can add a select('*') before the joins.

Like this:

return Metric

    ::select('*')

    ->join(...........

It will return a Metric model with all the attributes of the joined tables.

This example will return an object like:

Metric (
    ....
        attributes: [
            id => 0, // From metrics table
            name => 'somename', // Metrics table
            metric_id => 0, // metric_product table
            product_id => 0, // metric_product table
            ....
        ]
    ....
)

Since there are a lot of columns with the same name you won't be able to access these values. However you can multiple selects and rename the columns in your results.

Like this:

retrun Metric

    ::select([
        *,
        DB::raw('products.name as product_name'),
        ....
    ])

    ->join(....

This will result in something like:

Metric (
    ....
        attributes: [
            id => 0, // From metrics table
            name => 'somename', // Metrics table
            metric_id => 0, // metric_product table
            product_id => 0, // metric_product table
            product_name => 'somename' // products table
            ....
        ]
    ....
)

I hope this will solve something :) Of course there are much cleaner ways to solve your problem.

Solution 4

This should do the trick:

The Models/Product.php file:

class Product extends Model
{
    public function deals()
    {
        return $this->belongsToMany(Deal::class);
    }

    public function metrics()
    {
        return $this->belongsToMany(Metric::class);
    }
}

the Models/Deal.php file:

class Deal extends Model
{
    public function products()
    {
        return $this->belongsToMany(Product::class);
    }
}

the Models/Metric.php file:

class Metric extends Model
{
    public function products()
    {
        return $this->belongsToMany(Product::class);
    }
}

From your controller:

class ExampleController extends Controller
{
    public function index()
    {
        $deal = Deal::with('products.metrics')->get();
    }
}

Now you have a collection of deals with products, and their related metrics. See the docs for nested eager loading.

Solution 5

There is no "Many to Many Through" method available.

To get all Metrics available in all Products for a certain Deal, create a getter to Eager Load all Products and Metrics for the Deal, and leverage Laravel's Collections methods.

In your Deal model:

public function products()
{
    return $this->belongsToMany('App\Product');
}

public function getMetrics()
{
    return $this->products->lists('metrics')->collapse()->values();
}

You can then get the metrics for a Deal with $deal->getMetrics()

This will return a Collection of Metrics objects, which should be exactly what you are looking for.

View more solutions

Comments

[Ohgodwhy]

I went down the same rabbit hole at one point; here's a question I asked and answered for the community. I'm not 100% sure it's the cleanest solution to be honest, but I haven't had time to go back and rethink it. However, it does work and you can make it work across as many tables as you'd like to get that relationship Have a look here

[ntzm]

@Ohgodwhy That works! But I forgot to mention I need it to return the value column in metric_product in the standard pivot format. If you're not sure how to do this, I will create a new question.

[ntzm]

Thanks for this, but unfortunately I need to eager-load the relationship, and this doesn't let me do this.

[ntzm]

I think you misunderstood. In order to eager load the relations (Deal::with('metrics')), $deal->metrics() needs to return some an instance of Illuminate\Database\Eloquent\Relations\Relation.

[Thomas Van der Veen]

True, but in the example ::with() is not used. As the example says: use select(DB:raw([...]) to retrieve columns with ambiguous names. However you won't be able to use functionalities of the Product and Deal models. The choise depends on what you want to do with the metrics after retrieving.

[ntzm]

I did mention in the OP. >It needs to be in relationship format, because they need to be eager loaded for my use case. Thanks for your help

[Thomas Van der Veen]

Np :) I hope this answer is still useful in some other way.

[ntzm]

Unfortunately I need Deal to have a metrics method that returns a relation.

[cppcho]

the lists method becomes "pluck" in Laravel 5.3. So the line should be $this->products->pluck('metrics')

[Moe]

Can't understate how good this package is. The use of existing many-to-many relationships is so elegant and works beautifully. github.com/staudenmeir/…

[Frankie Jarrett]

Wow, this package really does what it says. Worked first try for me on a complex relationship using the pivot model. Could be a game changer for me and how I structure my models.

[Frankie Jarrett]

Equally impressive is their other package for "belongsToThrough()" github.com/staudenmeir/belongs-to-through