Laravel：对多个值进行多个过滤 [英] Laravel: Filter many to many on multiple values

问题描述

我正在为我的laravel应用程序构建分层导航模块 - 就像在Magento或WooCommerce中一样。这是一个想法：可以为产品分配单个或多个属性，用户应该能够使用这些属性过滤产品。像产品可以分配一个或多个值，如铁，木材& 塑料。我的问题是，我无法弄清楚如何正确地执行此操作。

我的数据模型是这样的：

  products table：id |名称|其他信息... 
示例：42 |椅子| ... 
示例：14 |自行车| ... 
 
属性表：id |名称| description 
 example：02 |材料|资料... 
 
 attribute_options表：id | attribute_id |值
示例：11 | 02 |木头
示例：12 | 02 | Iron 
 
数据透视表：id | product_id | attribute_option_id 
示例：12 | 42 | 11 
示例：12 | 42 | 12 
示例：12 | 14 | 12 
  

我已经将产品和产品选项模型（因此是数据透视表）。

在我的视图文件中，显示一个表单，循环遍历所有不同的属性，然后循环通过他们的选项，并为每个选项创建一个复选框，其值为其id 。这些值理想地组合在一个数组中，如下所示： filter [attribute_id] [attribute_option_id] 。一个例子：

 < input type =checkboxname =filter [02] [11]value =id > Wood 
< input type =checkboxname =filter [02] [12]value =id> Iron 
< input type =checkboxname =filter [xx] [xx]value =id> etc .. 
  

在提交表单时，所有选定的属性选项值都将发送到服务器到路由应该处理这些信息，只有返回符合所有不同标准的产品。

所以如果过滤器[02] [11]和[02] [12]被发布，只有具有Wood和Iron属性选项的产品应该被归还。

起初，我以为这会很简单，但是我觉得我不像我以为我会那样熟练，因为这是一个Laravel的问题，我很喜欢一个雄辩的样式解决方案！

PS如果我搞砸了（想到我的）数据模型，请告诉我！我还在学习很多关于网页开发的东西，也许有一个更好/更清洁的解决方案/方法我的问题

-------编辑/其他信息--------

使用以下代码，我可以过滤一个属性

  $ products = $ products-> whereHas（'attributeOptions'，function（$ query）
 {
 $ query-> where（function（$ query）
 {
 $ query-> where（'value'，'iron'）; 
}）; 
}） ; 
  

但是，由于产品可以有多种不同类型的属性（如颜色和材质或多种材质）能够设置如下这样的多个轮廓：

  $ products = $ products-> whereHas（'attributeOptions'，function $查询）
 {
 $ query-> where（function（$ query）
 {
 $ query-> where（'value'，'iron'）; 
 $ query-> where（'value'，'wood'）; 
 $ query-> where（'value'，'yellow'）; 
}）; 
 }）; 
  

但是这个代码不起作用，因为它检查一行有多个值而不是检查值在同一product_id的多行上。

产品和 attribute_options 之间的关系是：

  public function attributeOptions（）{
 
 return $ this-> belongsToMany（'AttributeOption'，'products_attribute_options' product_id'，'attribute_option_id'）; 
 
} 
  

这在Eloquent / Laravel还是需要的不同的解决方案

提前感谢，我很乐意向您学习！

解决方案

我完全同意@astro和@Dave从评论。你应该重新考虑你的数据库设计。我将从@Dave转发两个链接，以便他们获得更多的知名度：

如何在SQL中实现过滤器系统

作为关系表检索MySQL EAV结果的最佳性能

---

虽然我不认为这是在性能方面（有很多属性和产品），这里是一个解决方案，可以与您当前的设置配合使用：

将您的复选框更改为：

 < input type =checkboxname =filter [02] []value =11> Wood 
< input type =checkboxname =filter [02] []value =12> Iron 
< input type =checkboxname =filter [xx] []value =xx> etc .. 
  

这样一来，相同过滤器的多个值将作为数值数组添加到 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）
  - >其中（'attribute_option_id'，$ optionId ）; 
} 
} 
} 
 $ products = $ query-> get（）; 
  

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!

解决方案

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

这篇关于Laravel：对多个值进行多个过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！