构建查询以检索可用的产品颜色

Question

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: product_id, color_id
product_sizes: product_id, size_id
colors: id, value, hex
sizes: id, value

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:

Product::whereHas('categories', function ($q) use ($cat_ids) {
        return $q->whereIn('id', $cat_ids);
    })->with(['colors', 'sizes', 'brand'])->get();

EDIT: Retrieving colors and sizes:

$sizes = collect($products->pluck('sizes'))->flatten()->pluck('value', 'id');
$colors = collect($products->pluck('colors'))->flatten()->pluck('value', 'id');

Product.php:

public function categories() {
    return $this->belongsToMany(Category::class, 'product_category');
}

public function colors() {
    return $this->belongsToMany(Color::class, 'product_colors');
}

public function sizes() {
    return $this->belongsToMany(Size::class, 'product_sizes');
}

Category.php

public function products() {
    return $this->belongsToMany(Product::class, 'product_category');
}

Color.php

public function products() {
    return $this->belongsToMany(Product::class, 'product_colors');
}

Size.php

public function products() {
    return $this->belongsToMany(Product::class, 'product_sizes');
}

Answer 1

Here's a non-collection oriented method: loop through the products in a category, calling the color function. Add each result to an array. Once done, remove duplicates.

This will accomplish what you asked, but if you are looking to have the original products still attached to the colors (basically a different returned data structure), you'll need to specify what it should look like.