Fastest query? Count rows in relation after where()

Hi!

I'm looking to learn whether the following query is the best way to do what I'm after. Which, from the query I believe becomes clear.

Objective: To count all check-ins made today and with a certain status for a set of locations

Code:

// I start a new query
$location->newQuery();

// And do some simple queries on it...
$location->where('active', 1);
//...
//... more (conditional) stuff happening here

// Then the complex (inefficient?) query
$location
    ->leftJoin('checkins', 'locations.id', '=', 'checkins.location_id')
    ->selectRaw('locations.id, count(checkins.location_id) as no_checkins')
    ->whereDate('checkins.created_at', \Date::today())
    ->whereIn('checkins.status_id', Status::whereIn('code', array(210, 211, 212, 213))->pluck('id'))
    ->whereNotIn('checkins.status_id', Status::whereIn('code', array(220, 221, 222))->pluck('id'))
    ->groupBy('locations.id')
    ->get();

It seems to work (in Tinker, I haven't tested the entire set), but I'm doubtful this is the most effective (system resource wise) way to do this. Or...?

BillRiess

7 years ago

Best Answer

Level 5

You could take a more Eloquent approach to this and use query scopes and cache results. This would boost performance on future calls, you can set up an observer to update the cache when new records are added/removed.

Please or to participate in this conversation.