Laravel Relationships with 3 tables and two pivot tables

Question

Hi I used Laravel relationships with many to many pivot tables without problems but i can't get around the logic i need to write to create a relation between 3 models using 2 pivot tables to execute a query like the one below:

select * from product
left join product_to_category on product_to_category.product_id = product.product_id
left join category on product_to_category.category_id = category.category_id
left join category_to_brand on category_to_brand.category_id = category.category_id
left join brand on brand.brand_id = category_to_brand.brand_id
where brand.key = 'furniture-hire-uk'
and category.slug = 'chair-hire'

the table structure is as follows:

product product id some more feilds

category category id some more feilds

brand brand_id key some more feilds

product_to_category product_id category_id

category_to_brand category_id brand_id

It's pretty straightforwad Product::leftJoin()->leftJoin()->where()->where()->get([columns you need]) - have you tried that? — Jarek Tkaczyk
I know i can use do it using the query builder but i was asking about how to do it using Eloquent relations something like: Brand::where('key','=','furniture-hire-uk')->categories()->where('slug','=','chair-hire')->products; can i do something like that with relation ships or relation ships can't be used for 3 tables with two pivot tables ?? — Joseph Girgis

Jarek Tkaczyk Jarek Tkaczyk · Accepted Answer · 2014-07-25T07:39:36

Using relationships:

// assuming relations:
categories: Product belongsToMany Category
brands: Category belongsToMany Brand
// and tables according to your question:
product, category, brand

$slug = 'chair-hire';
$key = 'furniture-hire-uk';

Product::whereHas('categories', function ($q) use ($slug, $key) {
  $q->where('category.slug', $slug)
    ->whereHas('brands', function ($q) use ($key) {
      $q->where('brand.key', $key);
    });
})->get();

Or manual joins:

Product::join('product_to_category as pc', 'product.id', '=', 'pc.product_id')
       ->join('category', function ($j) use ($slug) {
         $j->on('category.id', '=', 'pc.category_id')
           ->where('category.slug', '=', $slug);
       })
       ->join('category_to_brand as cb', 'category.id', '=', 'cb.category_id')
       ->join('brand', function ($j) use ($key) {
         $j->on('brand.id', '=', 'cb.brand_id')
           ->where('brand.key', '=', $key);
       })
       ->get(['product.*'])

Laravel Relationships with 3 tables and two pivot tables

1 Answers