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

Question

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?

score 1 · Answer 1 · answered Oct 15 '22 at 09:46

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

thank you very much for your valuable contribution – Code Kris Oct 15 '22 at 18:49 — Code Kris, Oct 15 '22 at 18:49

score 1 · Accepted Answer · answered Oct 15 '22 at 18:39

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;');

it is working as i wish.thank a lot you save my lot of time – Code Kris Oct 15 '22 at 18:50 — Code Kris, Oct 15 '22 at 18:50

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

2 Answers2