Laravel Eloquent filter product's attributes

Question

I am making a filter function for products. I have 2 models: Product and Attribute (BelongsToMany relationship)

Attributes table:

id      name
-------------
1       RAM
2       HDD

Products table:

id      name
-----------------
1       Product 1
2       Product 2
3       Product 3
4       Product 4

And the pivot table look like:

id      attribute_id            product_id     value
----------------------------------------------------
1       1 (RAM)                 1              8GB
2       2 (HDD)                 1              256GB
3       1 (RAM)                 2              8GB
4       2 (HDD)                 2              256GB
5       1 (RAM)                 3              4GB
6       2 (HDD)                 4              512GB

When filter products, user select RAM => [4GB, 8GB] and HDD => 256GB.

I want to get products that have RAM - 4GB OR RAM - 8GB AND HDD - 256GB. It means product has attributes RAM - 4GB, HDD - 256GB AND RAM - 8GB, HDD - 256GB will be satisfied the filters. Compared to my above database, I will get products with ID 1 and 2 (Product 3 have RAM 4GB but doesn't have HDD 256, so doesn't count).

Here are my code, but it doesn't work as I expected.

$products = Product::whereHas('attributes', function ($query) {
            $query->whereIn('attribute_id', [1, 2]);
            $query->whereIn('value', ['4GB', '8GB', '256GB']);
        })->get();

or

$products = Product::whereHas('attributes', function ($query) {
            $query->where(function ($query) {
                $query->where('attribute_id', 1)
                    ->whereIn('value', ['4GB', '8GB']);
            })->orWhere(function ($query) {
                $query->where('attribute_id', 2)
                    ->whereIn('value', ['256GB']);
            });
        })->get();

Tarasovych Tarasovych · Accepted Answer · 2019-05-22T06:53:23

Try this instead:

$products = Product::whereHas('attributes', function ($query) {
    $query
        ->whereIn('attribute_id', [1, 2])
        ->whereIn('value', ['4GB', '8GB', '256GB']);
})->get();

You have to chain those whereIn().

In the method signature you can note the third optional $boolean parameter:

whereIn(string $column, mixed $values, string $boolean = 'and', bool $not = false)

Update: raw SQL. I'd rather go with raw Eloquent query in your case.

Laravel Eloquent filter product's attributes

1 Answers