CODEWITHSUNDEEP
laravel-4eloquent
Joseph Girgis
Joseph Girgis

Reputation: 3165

Laravel Relationships with 3 tables and two pivot tables

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

Upvotes: 1

Views: 1516

Answers (1)

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81147

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.*'])

Upvotes: 3

Related Questions