如何根据产品尺寸重量在价格表最值区间匹配获取对应价格

我有一个存储产品信息的表，希望实现定价自动化，为此创建了名为price的表，结构如下：

• max_height
• max_width
• max_long
• max_weight
• min_height
• min_width
• min_long
• min_weight
• price

我想根据产品的height、width、long、weight值获取对应的price，尝试了以下实现方式：

控制器代码 #

<?php

namespace App\Http\Controllers;


use App\Models\Coli;
use App\Models\Pricing;
use Illuminate\Pipeline\Pipeline;


class ColiPriceController extends Controller
{
    public static $data = [];
    
    public static function price($id){
        ColiPriceController::setData($id);

        $price = app(Pipeline::class)
            ->send(Pricing::query())
            ->through([
                \App\Filters\Pricing\MaxHeightPriceFilter::class,
                \App\Filters\Pricing\MinHeightPriceFilter::class,
                
                \App\Filters\Pricing\MaxLongPriceFilter::class,
                \App\Filters\Pricing\MinLongPriceFilter::class,
                
                \App\Filters\Pricing\MaxWidthPriceFilter::class,
                \App\Filters\Pricing\MinwidthPriceFilter::class,
                
                \App\Filters\Pricing\MaxWeightPriceFilter::class,
                \App\Filters\Pricing\MinWeightPriceFilter::class,
            ])
            ->thenReturn()
            ->first();
    }

    protected static function setData($id)
    {
        $coli = Coli::find($id);

        $coli->height = ($coli->height) ? intval($coli->height) : 0;
        $coli->width = ($coli->width) ? intval($coli->width) : 0;
        $coli->longeur = ($coli->longeur) ? intval($coli->longeur) : 0;
        $coli->wieght = ($coli->wieght) ? intval($coli->wieght) : 0;

        $data = [
            'height'    => $coli->height,
            'width'     => $coli->width,
            'long'      => $coli->longeur,
            'weight'     => $coli->wieght,
        ];
       
        return ColiPriceController::$data = $data;
    }
}

MaxHeightFilter代码 #

<?php

namespace App\Filters\Pricing;

use Closure;

class MaxHeightPriceFilter extends PriceFilter
{
    public $column = "max_height";
    public $dataColumn = "height";
    public $operator = "<=";

    public function handle($request, Closure $next)
    {
        return $this->filter($request, $next);
    }
}

PriceFilter代码 #

<?php

namespace App\Filters\Pricing;

use Illuminate\Database\Eloquent\Builder;
use App\Http\Controllers\ColiPriceController;


class PriceFilter 
{
    public $column = "max_weight";
    public $dataColumn = "weight";
    public $operator = "<=";

    protected function filter($request, Closure $next)
    {
        if($this->chequePriceToContinue($request)){
            return $next(static::removeWhere($request, $this->column));
        }
           
        return $next($request);
    }

    public function chequePriceToContinue($request){
        $price = $request->where($this->column, $this->operator,  ColiPriceController::$data[$this->dataColumn] )->get();
        if(is_array($price)){
            return true;
        }
        
        return false;
    }

    /**
     * @param Builder $builder
     * @param $whereColumn
     * @return Builder
     */
    public static function removeWhere(Builder $builder, $whereColumn)
    {
        $bindings = $builder->getQuery()->bindings['where'];
        $wheres = $builder->getQuery()->wheres;

        $whereKey = false;
        foreach ($wheres as $key => $where) {
            if ($where['column'] == $whereColumn) {
                $whereKey = $key;
                break;
            }
        }

        if ($whereKey !== false) {
            unset($bindings[$whereKey]);
            unset($wheres[$whereKey]);
        }

        $builder->getQuery()->wheres = $wheres;
        $builder->getQuery()->bindings['where'] = $bindings;

        return $builder;
    }
}

1. 静态变量并发污染：ColiPriceController::$data是静态变量，多请求并发时会互相覆盖数据，导致筛选条件完全错误。
2. 筛选逻辑完全失效：chequePriceToContinue方法中，get()返回的是Laravel Collection实例，不是数组，is_array($price)永远为false，所有筛选条件都不会被应用，最终查询会直接取第一条数据。
3. 无意义的条件移除逻辑：filter方法先添加查询条件，随后又移除，完全没有起到筛选作用。
4. 拼写/命名错误：MinwidthPriceFilter类名大小写不一致、wieght字段拼写错误，会导致运行时异常。

核心思路 #

正确构建区间查询条件，避免静态变量，简化过滤器逻辑，确保产品的尺寸/重量落在price表对应的区间内。

1. 重构控制器，移除静态变量

直接传递产品数据，避免并发污染：

<?php

namespace App\Http\Controllers;

use App\Models\Coli;
use App\Models\Pricing;
use Illuminate\Pipeline\Pipeline;

class ColiPriceController extends Controller
{
    public static function price($id)
    {
        $coli = Coli::findOrFail($id);
        
        // 修正拼写错误，整理产品数据
        $productData = [
            'height' => $coli->height ? intval($coli->height) : 0,
            'width' => $coli->width ? intval($coli->width) : 0,
            'long' => $coli->longeur ? intval($coli->longeur) : 0,
            'weight' => $coli->weight ? intval($coli->weight) : 0,
        ];

        $price = app(Pipeline::class)
            ->send(Pricing::query())
            ->through([
                new \App\Filters\Pricing\MinHeightPriceFilter($productData),
                new \App\Filters\Pricing\MaxHeightPriceFilter($productData),
                new \App\Filters\Pricing\MinLongPriceFilter($productData),
                new \App\Filters\Pricing\MaxLongPriceFilter($productData),
                new \App\Filters\Pricing\MinWidthPriceFilter($productData),
                new \App\Filters\Pricing\MaxWidthPriceFilter($productData),
                new \App\Filters\Pricing\MinWeightPriceFilter($productData),
                new \App\Filters\Pricing\MaxWeightPriceFilter($productData),
            ])
            ->thenReturn()
            ->first();
            
        // 处理无匹配价格的情况
        if (!$price) {
            throw new \Exception("未找到匹配的定价规则");
        }
        
        return $price;
    }
}

2. 重构过滤器基类，简化逻辑

直接添加查询条件，移除冗余逻辑：

<?php

namespace App\Filters\Pricing;

use Closure;
use Illuminate\Database\Eloquent\Builder;

class PriceFilter
{
    protected $column;
    protected $dataColumn;
    protected $operator;

    public function __construct(protected array $productData)
    {}

    public function handle(Builder $query, Closure $next)
    {
        $value = $this->productData[$this->dataColumn] ?? 0;
        $query->where($this->column, $this->operator, $value);
        
        return $next($query);
    }
}

3. 调整具体过滤器类

每个过滤器只需定义对应字段和操作符：

<?php

namespace App\Filters\Pricing;

class MaxHeightPriceFilter extends PriceFilter
{
    protected $column = "max_height";
    protected $dataColumn = "height";
    protected $operator = "<=";
}

<?php

namespace App\Filters\Pricing;

class MinHeightPriceFilter extends PriceFilter
{
    protected $column = "min_height";
    protected $dataColumn = "height";
    protected $operator = ">=";
}

其他过滤器（如MinWidth、MaxWeight等）按相同方式编写，注意修正类名和字段拼写。

4. 额外优化

• 添加数据库索引：给price表的区间字段添加组合索引，提升查询速度：

  CREATE INDEX idx_pricing_dimensions ON price(min_height, max_height, min_width, max_width, min_long, max_long, min_weight, max_weight);
  

• 数据验证：在控制器中添加产品尺寸/重量的合法性校验，避免无效数值进入筛选逻辑。

内容的提问来源于stack exchange，提问作者GACHTOU NourEddine