Is there anyway to retrieve count 0 when retrieve data using where clause? (Laravel Eloquent)-CodePudding

I'm currently working on retrieving data using group by to get the same treasure name, total found and the same treasure amount (cap). Using the code below, I'm able to get all treasures data but when the treasure is fully claimed, it unable to show that total_left is 0.

*claimed column is a boolean where 0 is not yet been claimed.

Query

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

Data

[
    {"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}
]

Desired Data

[
    {"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}
]

CodePudding user response：

Following is the actual records in DB:

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

Try this query to retrieve the data:

$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();

Following is the results of above mentioned query:

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

CodePudding user response：

You could do this with a CTE (Common Table Expression) and a DB::select statement. For example:

$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
");

Which will return the result set as an array.