如何按产品与包装类型分组查价格，优先指定company_id定价

表结构说明 #

prices表字段如下：

product_id  int 
company_id  int NULL    
amount  int 
packaging_type  enum('Drum','Pail','Tote','Bulk','Jug') 
effective_at    date

业务规则 #

• 通用定价以company_id=0标识，每个product_id对应多种包装类型的定价，按product_id和packaging_type分组后取MAX(effective_at)得到最新通用定价。
• 部分公司有特殊定价，使用对应company_id（关联companies表），优先选用该公司对应product_id和packaging_type的定价；如果没有特殊定价，则取对应分组的最新通用定价。
• 每个product_id/packaging_type/company_id组合仅存在一条定价。

当前实现代码 #

Laravel Eloquent代码 #

$sub = Price::select(
        'product_id',
        'packaging_type',
        DB::raw('MAX(effective_at) as current_at'),
        DB::raw('MAX(company_id) as company')
    )
    ->where('company_id', 0)
    ->groupBy('product_id', 'packaging_type');

$generalPricing = Price::join(
        DB::raw("({$sub->toSql()}) current_prices"),
        function($join) {
            $join->on('current_prices.product_id', '=', 'prices.product_id')
                ->on('current_prices.packaging_type', '=', 'prices.packaging_type')
                ->on('current_prices.current_at', '=', 'prices.effective_at')
        }
    )
    ->addBinding($sub->getBindings(), 'join')
    ->get();

对应的SQL语句 #

select *
from `prices`
inner join (
    select
        `product_id`,
        `packaging_type`,
        MAX(effective_at) as current_at,
        MAX(company_id) as company
    from `prices`
    where `company_id` in (?, ?)
    group by `product_id`, `packaging_type`
) current_prices
    on `current_prices`.`product_id` = `prices`.`product_id`
    and `current_prices`.`packaging_type` = `prices`.`packaging_type`
    and `current_prices`.`current_at` = `prices`.`effective_at`

需求痛点 #

目前仅实现了获取最新通用定价的逻辑，需要调整实现：先筛选出指定公司的特殊定价，再关联最新通用定价，但不知道如何实现所有产品/包装类型组合优先选用特殊定价的逻辑。

内容的提问来源于stack exchange，提问作者Chris Beaver