Level 70
Do you want the stores which contain a brand?
1 like
Jan 17, 2020
4
Level 1
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.
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
Level 70
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
Level 1
Thank you bro. Working... :)
Please or to participate in this conversation.