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!

Answer 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.

Answer 2

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.

Answer 3

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.

Answer 4

In case someone still needs it:

First you will eager load products and metrics

$deals->with('products.metrics');

in deal model

public function getMetricsAttribute(){
   return $this->products->pluck('metrics')->collapse();
}

now you can get all metrics of products of a deal by:

$deal->metrics

Answer 5

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

Answer 6

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.