How to get status count as seperate count in laravel query builder-CodePudding

I am working with a booking appointment system. I want to find out how many time slots are available and how many time slots are already booked. time slot count will be change base on the appointment. I used laravel to develop this system.I use the Appointment model and Time Model to store that data.

Appointments table =>id,user_id,date,status
Time table => id,appointment_id,status(0,1)

if some allocate the slot time status update as 1 (default 0). I want to appointment list with the appointment id, date and how many time slots are booked and how many slots are still available for each appointment (get a separate time slot count base on the status).I try follow code to archive this task.

 $appointments = DB::table('appointments')
                       ->join('times', 'times.appointment_id', '=', 'appointments.id')
                       ->where('appointments.status',0)
                       ->where('times.status',0)
                       ->selectRaw("appointments.id,appointments.date, COUNT('times.status') as status_count")
                       ->groupBy('appointments.id','appointments.date')
                       ->orderBy('appointments.id', 'asc')
                       ->get();

but it can only get a status 0 count only. how do get status 1 counted as separate column using single query?

CodePudding user response：

Try following the code. it is actual SQL code .you have to optimize this code according to the Laravel query builder mothod

$appointments = DB::select('select 
        appointments.id,appointments.date,
        sum(case when times.status = 0 then 1 else 0 end) as available,
        sum(case when times.status = 1 then 1 else 0 end) as booked
    from
        appointments
    INNER JOIN times
    ON appointments.id = times.appointment_id
    WHERE appointments.status = 0
    group by 
        appointments.id,appointments.date;');

CodePudding user response：

You can use the case together with the sum operator for conditional counting:

SELECT
  SUM(CASE WHEN 'times.status' = 0 THEN 1 ELSE 0 END) as inactive_count,
  SUM(CASE WHEN 'times.status' = 1 THEN 1 ELSE 0 END) as active_count
FROM ...

For the active_count it is also possible to simply apply the sum to the column itself (since they are 1 already): SUM('times.status')

You can now simply apply this logic to the laravel-query

Edit: See this post for an advanced example: https://stackoverflow.com/a/1288130/11028838