Laravel: filter a lot for many by multiple values

Question

I am creating a layered navigation module for my laravel application. Much has been done at Magento or WooCommerce. This is an idea: Products can be assigned one or more attributes, and users should be able to filter products using these attributes . Similar to the material attribute, where products can be assigned one or more values, such as iron, wood, and plastic. My problem is that I cannot figure out how to do this properly.

My datamodel is as follows:

products table: id | name | other info... example: 42 | Chair | ... example: 14 | Bike | ... attributes table: id | name | description example: 02 | Material | the material that the... attribute_options table: id | attribute_id | value example: 11 | 02 | Wood example: 12 | 02 | Iron pivot table: id | product_id | attribute_option_id example: 12 | 42 | 11 example: 12 | 42 | 12 example: 12 | 14 | 12 

I have established many, many relationships between the Product and Product Option models (hence the summary table).

My view displays a form that iterates over all the different attributes, and then iterates over their parameters and creates a flag for each of the parameters with the value of their identifier . Values ​​are ideally grouped together in one array and are named like this: filter [attribute_id] [attribute_option_id] . Example:

 <input type="checkbox" name="filter[02][11]" value="id"> Wood <input type="checkbox" name="filter[02][12]" value="id"> Iron <input type="checkbox" name="filter[xx][xx]" value="id"> Etc.. 

When submitting a form, all values ​​of the selected attribute value are sent to the server along the route on which this information should be processed, and only products that meet all different criteria should be returned.

So, if filters [02] [11] and [02] [12] were published, only products that have the attribute options "Wood" and "Iron" should be returned.

At first I thought it would be pretty simple, but I think I'm not as qualified as I thought I would ... Since this is a Laravel question, I would like it to be an eloquent style decision!

PS If I messed up (thinking for mine) a date model, please tell me! I am still learning a lot of things about web development and maybe there is a much better / cleaner solution / approach to my problem.

-------- EDIT / ADDITIONAL INFORMATION --------

With the following code, I can filter one attribute

 $products = $products->whereHas('attributeOptions', function($query) { $query->where(function($query) { $query->where('value', 'iron'); }); }); 

But since products can have several attributes of different types (for example, color and material or several materials), I need to be able to set several types:

 $products = $products->whereHas('attributeOptions', function($query) { $query->where(function($query) { $query->where('value', 'iron'); $query->where('value', 'wood'); $query->where('value', 'yellow'); }); }); 

But this code does not work, because it checks that a single line has multiple values ​​instead of checking values ​​for multiple lines with the same product_id.

Relationship between products and option_ attribute :

 public function attributeOptions() { return $this->belongsToMany('AttributeOption', 'products_attribute_options', 'product_id', 'attribute_option_id'); } 

Is this possible in Eloquent / Laravel or do I need another solution

Thank you in advance, I would like to know from you!

Answer 1

I totally agree with @astro and @Dave from the comments. You need to redesign your database. I will post two links from @Dave to make them more visible to others:

How to implement a filter system in SQL?

Better performance for getting MySQL EAV results in a relational table

---

Although I don't think this works well in terms of performance (with many attributes and products), here is a solution that should work with your current setup:

Change your checkboxes as follows:

 <input type="checkbox" name="filter[02][]" value="11"> Wood <input type="checkbox" name="filter[02][]" value="12"> Iron <input type="checkbox" name="filter[xx][]" value="xx"> Etc.. 

Thus, multiple values ​​for the same filter will be added as a numeric array to filter[02][]

 $query = Product::query(); foreach(Input::get('filter') as $attributeId => $optionIds){ foreach($optionIds as $optionId){ $query->whereHas('attributeOptions', function($q) use ($attributeId, $optionId){ $q->where('attributeId', $attributeId) ->where('attribute_option_id', $optionId); } } } $products = $query->get(); 

Answer 2

I would approach this using the whereHas method, and then using whereIn to select records that contain the desired attributes, and then filter them only on those records that have the correct number of attributes.

 // Assuming you have the IDs of the attributes, rather than the value $desiredAttributes = [11,12]; $products = $products->whereHas('attributeOptions', function($query) use($desiredAttributes) { $query->whereIn('attribute_id', $desiredAttributes); }, "=", count($desiredAttributes))->get(); 

So, first you need to get all the records that have attributes 11 or 12, and then filter only the records that have count (attribute_option_id) == 2