我正在研究某些产品的过滤器.我有大部分工作但是我遇到了一个不可能的where子句的错误.
该表包含单个产品的多个行,我尝试匹配每个产品的多个条件,这会导致它失败.
如果您对此有意见,或者可能是解决此问题的方法,我将非常感激.
数据库表如下所示：
--------------------------------------------
|id | FilterKey | filterValue | product_id |
--------------------------------------------
|1 | Colour | GunMetal | 1 |
|2 | Colour | Silver | 1 |
|3 | Size | 750cc | 1 |
|4 | Size | 1000cc | 1 |
|5 | Colour | Red | 2 |
|6 | Colour | Blue | 2 |
|7 | Size | 750cc | 2 |
|8 | Size | 1000cc | 2 |
--------------------------------------------
过滤器看起来像这样：
public function scopeFilterProduct($query,$filters)
{
$this->filters = $filters;
if (count ($this->filters) === 1 && isset($this->filters[0]))
{
return $query;
}
$query->join('product_filters','products.id','=','product_filters.product_id')->Where(function($query){
foreach ($this->filters as $filter => $vals)
{
$this->filter = $filter;
$this->vals = $vals;
$query->Where(function ($query){
$query->Where('filterKey',$this->filter);
$query->Where(function($query){
foreach ($this->vals as $val){
$query->orWhere('filterValue',$val);
}
$this->vals = null;
});
});
$this->filter = null;
};
});
return $query;
}
select
distinct
`products`.`id`,`product_id`
from
`products`
inner join
`product_filters`
on
`products`.`id` = `product_filters`.`product_id`
where
(
(`filterKey` = 'Colour' and (`filterValue` = 'gunMetal'))
and
(`filterKey` = 'Size' and (`filterValue` = '750cc'))
)
and
`products`.`deleted_at` is null
解决方法
你认为你添加的范围是错误的.在我看来,即使你的数据库结构也不正确.这是我如何构建这个：
过滤表
该模型将包含所有过滤器值.例如,颜色,大小等.以下是过滤表的结构：
----------------- |id | name | ----------------- |1 | Colour | |2 | Size | -----------------
所以你雄辩的模型会变成这样的：
class Filter extends Model
{
protected $fillable = ['id','name'];
public function products()
{
return $this->belongsToMany(Product::class,'products_filters');
}
}
产品表
您的产品型号变为：
class Product extends Model
{
public function filters()
{
return $this->belongsToMany(Filter::class,'products_filters');
}
}
products_filters表
完成上述更改后,以下是表格的结构：
-------------------------------------------- |id | filter_id | filterValue | product_id | -------------------------------------------- |1 | 1 | GunMetal | 1 | |2 | 1 | Silver | 1 | |3 | 2 | 750cc | 1 | |4 | 2 | 1000cc | 1 | |5 | 1 | Red | 2 | |6 | 1 | Blue | 2 | |7 | 2 | 750cc | 2 | |8 | 2 | 1000cc | 2 | --------------------------------------------
现在您只需查询过滤器表,然后获取所有过滤器的关联产品.之后,您只需编译一个独特产品列表.
Unqiue产品基于选定的过滤器.
$ids = [];
$products = new \Illuminate\Support\Collection();
foreach($filters as $filter) {
foreach($filter->products as $product) {
if(!in_array($product->id,$ids)) {
$ids[] = $product->id;
$products->push($product);
}
}
}
return view('results',compact('products'));
在您看来,您需要写：
@foreach($products as $product) // Your product block HTML @endforeach
