如何实现有效期升序+库存值最接近待出库量的多条件Order By排序

核心问题排查 #

你当前编写的排序逻辑本身符合「优先按有效期升序，同效期按差值绝对值升序」的需求，返回错误结果主要有两个原因：

• SQL拼接风险：orderByRaw中直接拼接待出库量变量时额外加了双引号，导致数值运算被识别为字符串运算，差值计算逻辑异常，同时存在SQL注入风险
• 多余语法冗余：id是库存表主键，groupBy('id')属于无意义分组，在开启SQL严格模式的数据库中会直接报错，多余的min(qty)聚合查询也可能干扰数据库执行计划
• （可选排查点）如果修复上述问题后结果仍不符合预期，请检查exp_date字段类型是否为date/datetime，若存储为字符串需确保格式统一（如统一为YYYY-MM-DD），否则会出现字符串排序不符合日期时序的问题

修复后代码 #

Laravel查询构造器版本 #

// 提前定义可用库存表达式，避免重复编写
$availableQty = \DB::raw('qty - pick');
$xsql = Storage::query()
    ->where('item_id', $outstanding->item_id)
    ->where($availableQty, '>', 0)
    ->where($availableQty, '<=', $outstanding->outstanding)
    ->orderBy('exp_date', 'asc')
    // 使用参数绑定传递变量，避免注入和语法错误
    ->orderByRaw("ABS(($availableQty) - ?)", [$outstanding->outstanding])
    ->first();

对应原生SQL #

SELECT *
FROM storages 
WHERE
    item_id = 1 
    AND qty - pick > 0 
    AND qty - pick <= 42 
ORDER BY
    exp_date ASC,
    ABS((qty - pick) - 42) ASC
LIMIT 1

扩展适配（可选） #

如果你的实际业务需要优先选择「可用库存足够覆盖待出库量」的批次，再按有效期、差值排序，可以额外增加一个排序优先级：

->orderByRaw("IF($availableQty >= ?, 0, 1)", [$outstanding->outstanding])

把这行放在orderBy('exp_date')之前即可。

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