laravel - 我们如何在 laravel eloquent/query builder 中转换 sql

Question

基本上我需要显示报告，其中我需要提供一些基于 cityId 和 postCode 的详细信息。

因此，如果 cityId 为 NULL 且 postCode 为 NULL，则这些记录的计数应在 data1 下。

同样， - 当 cityId IN (123, 456) THEN 这些记录的计数应该在“data2”中 - 当 cityId IN (789, 999) THEN 'data3' - 当 cityId IN (111, 222) THEN 'data4' - 否则 'data5'

我在这里有 2 个问题:

1. 将此 sql 转换为 laravel eloquent/querybuilder 方式。
2. 提到的最后一个条件

COUNT(CASE WHEN cityId IS NOT NULL AND cityId NOT IN (123, 456, 789, 999, 111, 222) then 1 ELSE NULL END ) as \"data5\" from customer ")

必须是上述所有条件的 else 部分。

我已经试过了，但没能达到预期的效果

$result = Customer::select(Customer::raw("SELECT
    COUNT(CASE WHEN cityId IS NULL AND postCode IS NULL then 1 ELSE NULL END) as \"data1\",
    COUNT(CASE WHEN cityId IN (123, 456) then 1 ELSE NULL END) as \"data2\",
    COUNT(CASE WHEN cityId IN (789,999) then 1 ELSE NULL END) as \"data3\",
    COUNT(CASE WHEN cityId IN (111, 222) then 1 ELSE NULL END) as \"data4\",
    COUNT(CASE WHEN cityId IS NOT NULL AND cityId NOT IN (123, 456, 789, 999, 111, 222) then 1 ELSE NULL END ) as \"data5\"
from trial
        "))->get();

期望的结果:数据1:7数据2:5数据 3:12数据4:10数据5:1

Answer 1

暂时，我得到了针对我上面提到的问题的替代解决方案，它以某种方式解决了我的第一个问题。

仍在寻找第二个问题。

DB::table('customers')
        ->select(
            DB::raw('COUNT(CASE WHEN cityId IS NULL AND postCode IS NULL then 1 ELSE NULL END) as "data1",
                    COUNT(CASE WHEN cityId IN (123, 456) then 1 ELSE NULL END) as "data2",
                    COUNT(CASE WHEN cityId IN (789,999) then 1 ELSE NULL END) as "data3",
                    COUNT(CASE WHEN cityId IN (4965,4959) then 1 ELSE NULL END) as "data4",
                    COUNT(CASE WHEN cityId IS NOT NULL AND cityId NOT IN (123, 456, 789, 999, 111, 222) then 1 ELSE NULl END ) as "data5"')
        )->get();