Efficiently count model multiple times based on column's value

I'm wondering if there is a better way to do this (or if I'm doing this right). I have a Booking Model, on the page I want to display a count of bookings in different states, based on a status column.

Right now I feel like I'm making too many calls to the database? Here is my code in my controller.

$pendingCount = Booking::where('status', 'pending')->count();
$canceledCount = Booking::where('status', 'canceled')->count();
$finishedCount = Booking::where('status', 'finished')->count();
ect...

Is this making 3 separate calls to the database? Is there a way I can make one call to get all bookings then "filter" them based on the status and count? I'm worried I'm not being efficient enough with Elloquent.

xuma

7 years ago

Level 9

You could try this.

$bookings = \DB::table('bookings')
            ->select(\DB::raw('status, count(*) as counts'))
            ->groupBy('status')
            ->get()->mapWithKeys(function ($items) {
                return [ $items->status => $items->counts];
            });
        dd($bookings);

Collection {#1910 ▼
 #items: array:14 [▼
   "Abuse" => 50
   "Answered" => 148
   "Closed" => 56548
   "In Progress" => 11
   "On Hold" => 7
 ]
}

And it runs this query;

select status, count(*) as counts from `bookings` group by `status`

Please or to participate in this conversation.