Laravel join with multiple tables

I'm having issues with joining three tables and getting the original table value back.

I have a parent table which is a store that needs to find the brand_id for the products within the store. I can't access the brand_id directly so I need to join, I have to join on to the stores product list then join that onto the product table which holds the brand_id.

$this_return = Store::with('address','setting')
                    ->leftjoin('StoreProducts', function ($join){
                        $join->on('StoreProducts.store_id', '=', 'stores.id');
                    })->leftjoin('products', function ($join) {
                        $join->on('products.id','=','StoreProducts.product_id');
                })
                ->where('products.brand_id', '=', $brandID)
                ->isActive()
                ->get();

This is returning a product value, But I wanted it to return all the stores the original table back if the products.brand_id was equal to the $brandID then return the current store and repeat for all stores.

Hope that makes sense Any help would be great.

tisuchi

6 years ago

Level 70

@brad_fresh99

Do you want the stores which contain a brand?

1 like

Brad_Fresh99

6 years ago

Level 1

@tisuchi I want to filter out the stores with a specific brand, but to find out if the store has the specific brand of product I have to go through two tables and match ids. The store table does not contain a brand_id it has a store_id, the StoreProducts has a store_id and product_id and the product table has a id and brand_id

tisuchi

6 years ago

Best Answer

Level 70

@brad_fresh99

I think your relations goes like this:

Stores -> (n*n)StoreProducts -> Products -> (n*1)Brands

which StoreProducts is a joining table between Stores and Products. With this assumption, I would use something like this:

In the Store model,

public function products(){
    return $this->belongsToMany(\App\Product::class, "store_products"); 
}

In the Products model,

public function stores(){
    return $this->belongsToMany(\App\Store::class, "store_products");
}

In the StoreProducts model,

public function store(){
    return $this->belongsTo(\App\Store::class);
}

public function product(){
    return $this->belongsTo(\App\Product::class);
}

This way the relationship is created. Then you can use something like this:

$stores = \App\Store::whereHas("products", function($q) use ($brandId){
    return $q->where("brand_id", $brandId);
});

This will give you the stores in which the brand exists.

2 likes

Brad_Fresh99

6 years ago

Level 1

Thank you bro. Working... :)

Please or to participate in this conversation.