使用where子句检索数据时，是否仍要检索计数0？（拉雷维尔口若悬河）

Question

我目前正在使用群组检索数据，以获得相同的宝藏名称，总发现和相同的宝藏数量（上限）。使用下面的代码，我可以得到所有的宝藏数据，但是当宝藏被完全认领时，它无法显示total_left是0。

*声明的列是一个布尔值，其中0尚未声明。

查询

$treasure_hunt_data = TreasureHunt::where('claimed', '0')
                        ->selectRaw(" treasure_name, count(claimed) as total_left, cap")
                        ->groupBy(['treasure_name', 'cap'])
                        ->get();

数据

[
    {"treasure_name":"Location A","total_left":5,"cap":5},
    {"treasure_name":"Location B","total_left":2,"cap":2},
    {"treasure_name":"Location C","total_left":2,"cap":2},
    {"treasure_name":"Location D","total_left":10,"cap":10}
]

期望数据

[
    {"treasure_name":"Location A","total_left":5,"cap":5},
    {"treasure_name":"Location B","total_left":2,"cap":2},
    {"treasure_name":"Location C","total_left":2,"cap":2},
    {"treasure_name":"Location D","total_left":10,"cap":10},
    {"treasure_name":"Location E","total_left":0,"cap":1}
]

Answer 1

以下是数据库中的实际记录：

[{"id":1,"name":"Location A","value":1,"total":"10","created_at":null,"updated_at":null},
{"id":2,"name":"Location B","value":1,"total":"220","created_at":null,"updated_at":null},
{"id":3,"name":"Location C","value":1,"total":"42","created_at":null,"updated_at":null},
{"id":4,"name":"Location D","value":0,"total":"23","created_at":null,"updated_at":null},
{"id":5,"name":"Location A","value":1,"total":"233","created_at":null,"updated_at":null}]

尝试此查询以检索数据：

$treasure_hunt_data = TreasureHunt::select(
            'treasure_name',
            \DB::raw('sum(case when claimed = 1 then 1 else 0 end) as total_left'), 
            \DB::raw('sum(cap) as cap')
        )
        ->groupBy('treasure_name')
        ->get();

以下是上述查询的结果：

[{"name":"Location A","total_left":"2","total":243},
{"name":"Location B","total_left":"1","total":220},
{"name":"Location C","total_left":"1","total":42},
{"name":"Location D","total_left":"0","total":23}]

Answer 2

您可以使用CTE（公共表表达式）和DB:：select语句来实现这一点。例如：

$treasure_hunt_data = DB::select("
    with treasure_data as (
        select treasure_name, count(claimed) as total_left, cap
        from treasure_hunt
        where claimed = 0 
        group by treasure_name, cap
    )
    select * from treasure_data where total_left = 0
");

它将以数组的形式返回结果集。