Level 65
Want something like a hasManyThrough()? http://laravel.com/docs/4.2/eloquent#has-many-through
Nov 17, 2014
7
Level 1
[L4] Eloquent Sub Query data manupulation
Hi,
I'm a bit stuck with my eloquent relationships. Been banging my head around this for some time.
My DB Structure:
Categories: :id :category_name :category_slug
Products: :id :product_name :product_slug :brand_id
Brands: :id :brand_name :brand_slug
Pivot tables exists between category and product & product and brand. No direct relationship between category and brand.
What i want to do is fetch all brands for a single category with some product details.
//In Category Model
public function product()
{
return $this->belongsToMany('Product','category_product');
}
//In Product Model
public function category()
{
return $this->belongsToMany('Category', 'category_product');
}
public function brand()
{
return $this->belongsToMany('Brand', 'brand_product');
}
//In Brand Model
public function products()
{
return $this->belongsToMany('Product', 'brand_product');
}
My Current Code for fetching all brands for a single category
Category::with(array('product' => function($q)
{
$q->with('brand')->get();
}))
->where('category_slug',$category_slug)
>first();
This simply return all the products of that category with brand associated with that product.
{
id: 10,
category_name: 'ABC',
category_slug: 'abc',
product: [
- {
id: 1,
-pivot: {
category_id : 10,
product_id : 1
},
-brand: [
- {
id: 2,
brand_name: 'Xyz Abc',
brand_slug: 'xyz_abc',
-pivot: {
product_id : 1,
brand_id : 1
}
}
]
},
- {
id: 2,
-pivot: {
category_id : 10,
product_id : 2
},
-brand: [
- {
id: 3,
brand_name: 'Xyz Abc',
brand_slug: 'xyz_abc',
-pivot: {
product_id : 2,
brand_id : 3
}
}
]
}
]
}
What i want is
{
id: 10,
category_name: 'ABC',
category_slug: 'abc',
product: [
- {
id: 1,
-brand: [
- {
id: 2,
brand_name: 'Xyz Abc',
brand_slug: 'xyz_abc',
}
]
},
- {
id: 2,
-brand: [
- {
id: 3,
brand_name: 'Xyz Abc',
brand_slug: 'xyz_abc'
}
]
}
]
}
I know the easiest way is to create a pivot table for category and brand, but is there any way to do it without creating one?
Level 1
But with a hasManyThrough() relation don't i need to have a category_id in my products for it to work?
Level 52
Rather looks like a many through many problem. There was an interesting answer there.
Level 1
After checking the post, I came up with this..
return DB::table('products')
->join('category_product', 'products.id', '=', 'category_product.product_id')
->join('categories', 'category_product.category_id', '=', 'categories.id')
->join('brands', 'products.brand_id', '=', 'brands.id')
->select('brands.*')
->where('categories.category_slug', '=', $category_slug)
->orderBy('products.id','asc')
->get();
This takes me half way through, tweaking it right now. Was wondering if there is any other optimal way of doing this.
Level 52
You said first that you have a pivot table between products and brands but when i read your query i see a brand_id in products table, so I wonder...
You should create good relations in your models. So you could do eager loading instead of your joins.
Level 65
brand_id would relate to a hasOne()?
Level 1
@bestmomo yes there is a pivot table between products and brands, but the previous developer for some reason had kept a brand_id in the products table aswell. But yes i agree, the best way would be to create a pivot_table between brands and categories.
Please or to participate in this conversation.