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?