I have two related Models in Laravel (8.0)
Products
--------- ----------- ----------------- -----
| item_id | group | show_in_website | ... |
--------- ----------- ----------------- -----
| 147 | Keyboards | 1 | ... |
| 158 | Keyboards | 1 | ... |
| 003 | Mouse | 1 | ... |
--------- ----------- ----------------- -----
and
Attributes
---- --------- ------------- ------------
| id | item_id | name | value |
---- --------- ------------- ------------
| 1 | 147 | Color | Black |
| 2 | 147 | Sensitivity | 1800 DPI |
| 3 | 158 | Color | White |
| 4 | 158 | Sensitivity | 1800 DPI |
| 5 | 003 | Color | White |
| 6 | 003 | Type | Mechanical |
| 7 | 003 | Size | 108 Keys |
---- --------- ------------- ------------
I have Products related to Attributes using hasMany() relationship, and i want users to filter Products based on one or more attributes. The only solution I could come up with was something like
$group = 'Mouse';
$names = ['Color', 'Sensitivity'];
$values = ['Black', 'White', 'Red', '1800 DPI', '2100 DPI']
// OR
$group = 'Keyboard';
$names = ['Type'];
$values = ['Mechanical']
/* These parameters are user controlled, user can only filter one group at a time,
but might choose one or more names/values pairs for filters.
I.E the first case would return all Mice which has Black,White or Red color and 1800 or 2100 DPI
*/
$products = Product::where(['show_in_website', 1], ['group', $group])
->whereHas('attributes',
function ($query) use ($names, $values){
$query
->whereIn('name', $names)
->whereIn('values', $values);
})->paginate(20);
The problem with this query is, If user selects Color: Black and Senstivity:1800DPI, it returns all products with Black Color (and whatever sensitivity) AND all products with 1800 DPI (and whatever color), not products which has BOTH properties uniquely.
How can I write an eloquent query to achieve the desired function? If not, is there a better way to do this? (Better modeling/code structuring)?
CodePudding user response:
You need to take the inputs in another format
$group = 'Mouse';
$attributes = ['Color' => ['black'], 'Sensitivity' => ['1800 DPI', '2100 DPI']];
And then loop on the attributes with a whereHas
$query = Product::where(['show_in_website', 1], ['group', $group]);
foreach ($attributes as $name => $values) {
$query->whereHas('attributes', function ($attributeQuery) use ($name, $values){
$attributeQuery->where('name', $name)
->whereIn('values', $values);
}
$products = $query->paginate(20);
CodePudding user response:
The way I did this was the following:
$products = Product::where('show_in_website', 1)->where('group', $group);
foreach($names as $name){
$products->whereHas('attributes', function ($query) use($name, $values){
$query->where('name', $name)
->whereIn('value', $values);
});
}
Thanks to Laravel Discord Community for helping me find this!