11 months ago

Fastest query? Count rows in relation after where()

Posted 11 months ago by axtg


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


// I start a new query

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

// Then the complex (inefficient?) query
    ->leftJoin('checkins', '', '=', 'checkins.location_id')
    ->selectRaw(', 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'))

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...?

Please sign in or create an account to participate in this conversation.