Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

stefanastecom's avatar

Laravel MySQL apply product filters with eloquent (what is the correct query?)

I'm trying to filter my products with eloquent but my where statement for the filters would never work the way I have it currently.

My database tables look like this:

The products table

+---------------+
| id | name     |
+---------------+
| 1  | product1 | 
| 2  | product2 |
| 3  | product3 | 
| 4  | product4 |
+---------------+

The properties table

+------------------------------------+
| id | property_group_id | value(int)|
+------------------------------------|
| 1  | 1                 | 20        |
| 2  | 1                 | 10        |
| 3  | 2                 | 2         |
| 4  | 2                 | 4         |
+------------------------------------+

The products_properties table

+--------------------------+
| product_id | property_id | 
+--------------------------|
| 1          | 1           |
| 1          | 3           |
| 2          | 2           |
| 2          | 4           |
+--------------------------+

The SQL I'm currently generating with Eloquent looks like this:

select * from `products` 
    where exists (
        select * from `properties`
            inner join `products_properties` 
                on `properties`.`id` = `products_properties`.`property_id` 
            where `products`.`id` = `products_properties`.`product_id` and 
            (
                `properties`.`property_group_id` = 1 and <--- property_group_id is not 
                `properties`.`value` >= 15 and                1 and 2 at the same time
                `properties`.`value` <= 25
            ) 
            and 
            (
                `properties`.`property_group_id` = 2 and
                `properties`.`value` >= 1 and 
                `properties`.`value` <= 2
            )
    )

I'm looking for product1 with this query but this will never happen since the property_group_id's don't match at the same row. Using an OR between the 2 where statements will also not work since only 1 of them has to be true to find something.

The SQL is generated like this in Eloquent:

$products = Product::with(['properties' => function($query){
        $query->with('propertyGroup');
    }])
    ->whereHas('properties', function ($query) {
        // Use filter when filter params are passed
        if(array_key_exists('filterGroupIds', $this->filter) && count($this->filter['filterGroupIds']) > 0){

            // Loop through filters
            foreach($this->filter['filterGroupIds'] as $filter){
                // Add where for each filter
                $query->where(
                    [
                        ["properties.property_group_id", "=", $filter['id']], // 1 or 2
                        ["properties.value", ">=", $filter['min']], // 15 or 1
                        ["properties.value", "<=", $filter['max']]  // 1 or 2
                    ]
                );
            }
        }

    })
    ->get();

What will be the correct query to get the right result back? And if possible how will my Eloquent code look like to generate this query?

0 likes
3 replies
staudenmeir's avatar
Level 24

Try one whereHas() clause per $filter:

$products = Product::with('properties.propertyGroup');

if(array_key_exists('filterGroupIds', $this->filter) && count($this->filter['filterGroupIds']) > 0) {
    foreach($this->filter['filterGroupIds'] as $filter) {
        $products->whereHas('properties', function ($query) {
            $query->where([...]);
        });
    }
}
stefanastecom's avatar

Thanks alot that works great! Does this fire an extra query for each filter?

staudenmeir's avatar

It executes one where exists (...) subquery per filter. Is that what you mean?

Please or to participate in this conversation.