Laravel: Filter many to many on multiple values

Question

I'm building a layered navigation module for my laravel application - Much like is done in Magento or WooCommerce. This is the idea: Products can be assigned single or multiple attributes and users then should be able to filter the products down by using these attributes. Like the attribute "material" where products can be assigned one or more values like iron, wood & plastic. My problem is that I can't figure out how to do this properly.

My datamodel is like this:

         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've set a many to many relation between the Product and Product Option models (hence the pivot table).

In my view file a form is displayed which loops over all the different attributes and then loops over their options and creates a checkbox for each of the options with a value of their id's. The values are ideally grouped together in one array and named like this: filter[attribute_id][attribute_option_id]. An 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..

On submitting the form all the selected attribute option values are send to the server to a route where this information should be processed and only products that meet all different criteria should be returned.

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

At first, I thought this would be quite simple, but I think I'm not as skilled as I thought I would be... Since this is a Laravel question, I'd love for a eloquent style solution!

P.S. If I messed up the (thinking behind my) datamodel, please tell me! I'm still learning a lot of stuff 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 for one attribute

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

But since products can have multiple attributes of different types (like color and material or multiple materials) I need to be able to set multiple wheres like this:

$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 doesn't work since it checks for one row having multiple values instead of checking for values on multiple rows with the same product_id.

The relation between products and attribute_options is:

public function attributeOptions() {

    return $this->belongsToMany('AttributeOption', 'products_attribute_options', 'product_id', 'attribute_option_id');

}

Is this even possible in Eloquent/Laravel or do I need a different solution

Thanks in advance, I'd love to learn from you!

Answer 1

I would approach this by using the whereHas method, and then using a whereIn to isolate records which contain the desired attributes, then filtering down that to only those records which 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 this would first go get all the records which have attributes 11 or 12, and then filter down to only records which have count(attribute_option_id) == 2

Answer 2

I totally agree with @astro and @Dave from the comments. You should rethink your database design. I'll repost the two links from @Dave here so they gain more visibility for others:

How to implement filter system in SQL?

What is best performance for Retrieving MySQL EAV results as Relational Table

---

While 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 to this:

<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..

This way 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();