将含Count、Filter、WhereIn的SQL转换为Laravel Query Builder求助

安全阿岳

2026-5-21

解决方案：将PostgreSQL FILTER统计转换为Laravel Query Builder写法

没问题，我来帮你搞定这条SQL到Laravel Query Builder的转换！你的原SQL用了PostgreSQL专属的FILTER子句来分组统计不同订单状态的数量，Laravel Query Builder虽然没有直接对应这个方法，但我们有两种完美的实现方式：

方案1：直接复用PostgreSQL FILTER逻辑（推荐PostgreSQL环境使用）

这种写法完全和你的原SQL逻辑一致，用selectRaw()直接嵌入原生的FILTER语法，保证结果和原查询完全匹配：

use Illuminate\Support\Facades\DB;

$orderStats = DB::table('order_item_histories')
    ->selectRaw("count(*) FILTER (WHERE order_status_id IN (5, 4, 15)) AS ready_for_delivery")
    ->selectRaw("count(*) FILTER (WHERE order_status_id IN (6)) AS out_for_delivery")
    ->selectRaw("count(*) FILTER (WHERE order_status_id IN (4, 10, 9, 12, 7)) AS completed_order")
    ->selectRaw("count(*) FILTER (WHERE order_status_id IN (11, 16, 17, 13)) AS waiting_for_cancellation_return_change")
    ->first();

// 访问统计结果示例：
// $orderStats->ready_for_delivery;
// $orderStats->out_for_delivery;

方案2：通用CASE WHEN写法（兼容多数据库）

如果你的项目可能切换到MySQL等其他数据库，推荐用SUM(CASE WHEN...)的通用写法，逻辑和原SQL完全等价，同时适配更多数据库环境：

use Illuminate\Support\Facades\DB;

$orderStats = DB::table('order_item_histories')
    ->selectRaw("SUM(CASE WHEN order_status_id IN (5, 4, 15) THEN 1 ELSE 0 END) AS ready_for_delivery")
    ->selectRaw("SUM(CASE WHEN order_status_id IN (6) THEN 1 ELSE 0 END) AS out_for_delivery")
    ->selectRaw("SUM(CASE WHEN order_status_id IN (4, 10, 9, 12, 7) THEN 1 ELSE 0 END) AS completed_order")
    ->selectRaw("SUM(CASE WHEN order_status_id IN (11, 16, 17, 13) THEN 1 ELSE 0 END) AS waiting_for_cancellation_return_change")
    ->first();