how to correctly use CASE WHEN with laravel Eloquent?

September 20, 2022

i want to display call and chat seconds and i need to group the data without repeating session_id
and for this I am using CASE WHEN designating that if type = "chat" it shows me only the chat ones and type = "call" only the call ones

but the problem is that the WHEN CASE only shows me the type of chat and adding null value to the call.

my code

$sub = DB::table('session_details')
    ->select('session_id',
    DB::raw('CASE WHEN type = "chat" THEN time_in_secs END AS chat_time_sec'),
    DB::raw('CASE WHEN type = "call" THEN time_in_secs END AS call_time_sec'),
    DB::raw('ROUND(SUM((spent_points/'.$valor_poinst_value->puntos_por_dolar.')),2) as total_sales'))
    ->whereIn('session_id', $ids_session)
    ->whereDate('created_at','>=',$from)
    ->whereDate('created_at','<=',$to)
    ->groupBy('session_id')
    ->get();

data result the my query code

{
    "session_id": "-N5fKtOYHJNvSy6Jj0Lj",
    "chat_time_sec": 5,
    "call_time_sec": null,
    "total_sales": "0.05"
},
{
    "session_id": "-N5fLhJ_kEa7RZWpASLu",
    "chat_time_sec": 7,
    "call_time_sec": null,
    "total_sales": "0.30"
}...

result that I hope to obtain

{
    "session_id": "-N5fKtOYHJNvSy6Jj0Lj",
    "chat_time_sec": 5,
    "call_time_sec": 15,
    "total_sales": "0.05"
},
{
    "session_id": "-N5fLhJ_kEa7RZWpASLu",
    "chat_time_sec": 7,
    "call_time_sec": 15,
    "total_sales": "0.30"
}

>Solution :

You need to add an aggregation function to your case, MAX shoudl do the trick

$sub = DB::table('session_details')
    ->select('session_id',
    DB::raw('MAX(CASE WHEN type = "chat" THEN time_in_secs END) AS chat_time_sec'),
    DB::raw('MAX(CASE WHEN type = "call" THEN time_in_secs END) AS call_time_sec'),
    DB::raw('ROUND(SUM((spent_points/'.$valor_poinst_value->puntos_por_dolar.')),2) as total_sales'))
    ->whereIn('session_id', $ids_session)
    ->whereDate('created_at','>=',$from)
    ->whereDate('created_at','<=',$to)
    ->groupBy('session_id')
    ->get();