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

Question

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?

Answer 1

Use 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([...]);
        });
    }
}

Answer 2

SELECT * FROM properties p 
WHERE p.value BETWEEN minval AND maxval
JOIN product_properties pp ON p.id = pp.property_id
JOIN products pr ON pr.id = pp.product_id

Note that this query hasn't been optimized at all since i don't know exactly the desired data and behaviour Also the filtering is made by the property id, not by the group id, if you want by the group just change

p.id = pp.property_id

to

p.property_group_id = pp.property_id

For the eloquent part, try doing it on your own and post the code, you need to define the relations between the models firstly though