Question 17656 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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;');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search