Level 5
can a Product only have 1 Color? if yes, you should have color_id in the Products table
Nov 4, 2017
7
Level 1
Help with building a query to retrieve colors
I have the following db structure:
products: id, product_id, name, desc, etc...
categories: id, name, slug
product_category: product_id, category_id
product_colors: id, product_id, hex, name
I also have the route: /category/{slug}, where a user sees all products under a category, with a sidebar for filtering them by price and other categories. What is the best way to retrieve all product colors specifically the products that were loaded under a category, distinct values also, so there won't be duplicate colors.
Query to retrieve products:
$products = Product::whereHas('categories', function($q) {
$q->whereIn('id', [1, 2, 4, 6]);
});
Product.php:
public function categories() {
return $this->belongsToMany(Category::class, 'product_category');
}
public function colors() {
return $this->hasMany(ProductColor::class);
}
Category.php
public function product() {
return $this->belongsToMany(Product::class, 'product_category');
}
ProductColor.php
public function product() {
return $this->belongsTo(Product::class);
}
Level 1
@vapenation No, a product can have multiple colors.
Level 1
$productIds = array_column($products->toArray(), 'id');
$productColors = ProductColor::whereIn('product_id', $productIds)->groupBy('id')->get();
You can also change group by column to match your needs.
Level 1
@kpasokhi How badly will that affect performance if I have 2000 products for example?
Level 5
I think you should have a Colors tables and a join table product_color, given what you have provided for your DB scheme I don't see it working
Level 1
@PaulDiamant I don't think there will be a problem with 2000 products. But if you are concerned so much about performance (and there are ten thousands of products in each category) you can cache the query.
Caching usually makes things more complicated so do it if you really need it.
Level 1
@vapenation I did this just now and added 2 tables, and changed the product_colors and product_sizes table to a belongsToMany relationship basically.
colors:
id, value, hex
sizes:
id, value
product_colors:
product_id, color_id
product_sizes:
product_id, size_id
And now my query looks like:
$cat_ids = [1, 2, 3, 4];
$products = Product::whereHas('categories', function ($q) use ($cat_ids) {
return $q->whereIn('id', $cat_ids);
})->with(['colors', 'sizes', 'brand'])->get();
$colors = collect($products->pluck('colors'))->flatten()->pluck('value', 'id');
dd($colors->toArray());
The results:
array:6 [▼
1 => "Yellow"
3 => "Black"
5 => "Orange"
4 => "Gray"
2 => "White"
6 => "Purple"
]
Please or to participate in this conversation.