Get unique relation rows with distinct in Laravel-CodePudding

Let's say I have Category model with hasMany relation to Product model. Product has color column.

I want to return all categories with all colors that exists in this category, so I tried:

return Category::with(['products' => function ($query) {
    $query->distinct('color');
}])->get();

Thanks to this I could later foreach or pluck category->products to get unique colors list. I know I can just get all products in every category, and then filter unique colors, but by doing this I would have to query for example 1000 products per category, instead just 5, which is unnecessary resource heavy. That's why I'm trying to do this on SQL level not PHP.

But this code does not work. There are no errors, it just still returns all products with duplicated colors. Why?

Edit:

Not sure why but my code works if I add select() with used columns before discrinct, and then distinct is making unique rows by all choosed columns. No "color" param required in distinct. Not sure why it works that way, need to dive deeper into SQL docs.

CodePudding user response：

Have you tried this code? Somehow, this will reduce your unnecessary query.

$categories = Category::with([
        'products'=> fn($q) => $q->select(['category_id', 'color'])->distinct('color')
    ])
    ->select('id') // select required column in categories table
    ->whereHas('products')
    ->get();

$colors = $categories->map(function($category) {
        return $category->products->pluck('color');
    })->toArray();

$color = [];

for ($i=0; $i < count($colors); $i  ) {
    $color = array_merge($color, $colors[$i]);
}

$uniqueColor = array_unique($color);

return $categories;