axtg

Fastest query? Count rows in relation after where()

Posted 4 months ago by axtg

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

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

Reply to

Use Markdown with GitHub-flavored code blocks.