How can I optimize a query that loops over each day of a month or loops over a given date range?

The following function is called 3 times from a controller with different parameters. Each time 30/31 queries are running according to the dates in a month. In the index page this single function is making 90-93 queries in a single page. How can I optimize it?

Here is the function:

function attendanceData($dates, $status_id, $department)
{
    $attendanceresult = [];

    foreach($dates as $date)
    {
        $presents = Attendance::with('Employee', function($q) use($department)
                                {
                                    $q->where('department_id', '=', $department)
                                        ->where('designation_id', '!=', 26);
                                })
                                ->whereDate('in_time', $date)
                                ->whereIn('attendance_status_id', $status_id)
                                ->count();

        if($presents)
        {
            $attendanceresult[] = $presents;
        }else
        {
            $attendanceresult[] = 0;
        }
    }

    return $attendanceresult;
}

The function is called with the following parameters:

$absentresult = attendanceData($dates, [4], $department = false);
    $totalPresentResult = attendanceData($dates, [1, 2, 3, 5], $department = 3);
    $totalAbsentResult = attendanceData($dates, [4], $department = 3);

One more thing to note here is I need the data in an array with each days data in an individual key => value pair. Like:

array:10 [
 0 => 7
 1 => 0
 2 => 7
 3 => 8
 4 => 3
 5 => 12
 6 => 13
 7 => 12
 8 => 0
 9 => 12
]

tykus

3 years ago

Level 104

@wakil ahmed please format your code correctly by wrapping it within triple backticks

```
// your code
```

2 likes

Wakil_Ahmed

3 years ago

Level 1

@tykus Done.

dysentry30

3 years ago

Level 1

If you place query in for loop, it will perform n queries. You can try this.

function attendanceData($dates, $status_id, $department)
{
    $attendanceresult = [];
    $attendance = Attendance::with('Employee')->get()->where('department_id', '=', $department)->where('designation_id', '!=', 26);

    foreach ($dates as $date) {
        $presents = $attendance->whereDate('in_time', $date)->whereIn('attendance_status_id', $status_id)->count();

        if ($presents) {
            $attendanceresult[] = $presents;
        } else {
            $attendanceresult[] = 0;
        }
    }

    return $attendanceresult;
}

kokoshneta

3 years ago

Level 27

@dysentry30 That’s still placing the query in the foreach loop; it will execute just as many queries as the version in the question.

kokoshneta

3 years ago

Level 27

@dysentry30 Ah, sorry, I missed the ->get() call outside the loop. There are still a few things wrong, though:

You should call the where clauses in the database, not on the collection. There’s no point in retrieving and storing all attendances and then filtering out the ones that match in PHP. At the very least, the query should be Attendance::where('department_id', $department)->where('designation_id', '!=', 26)->whereIn('attendance_status_id', $status_id)->get(), with the attendance status filter in the query and ->get() at the end.
You can’t use whereDate() on a collection instance; that’s a query builder method.

kokoshneta

3 years ago

Level 27

You’re querying the database individually for each date instead of just making a single query that includes all the desired dates and then grouping by the date.

You’re also loading a relationship (Employee) seemingly without ever using it, since you only return the row count, nothing else. That seems needlessly wasteful.

For what you’re actually returning, I think this should be sufficient (untested, but should work, as far as I can tell):

function attendanceData($dates, $status_id, $department) {
	return Attendance::select(DB::raw('COUNT(*) as total'))
		->whereIn('in_time', $dates)
		->whereIn('attendance_status_id', $status_id)
		->groupByRaw('DATE(in_time)')
		->get()
		->pluck('total')
	;
}

That should return an array of the structure you’re after.

1 like

kokoshneta

3 years ago

Level 27

Alternatively, if you want to avoid all the raw SQL statements in your query, you can select the dates, then filter them in the collection in PHP. That will be slightly slower than doing it in the database, but if we’re talking less than a thousand rows, we’re talking microseconds – nothing that will be noticeable.

function attendanceData($dates, $status_id) {
	return Attendance::select('in_time')
		->whereIn('attendance_status_id', $status_id)
		->whereIn('in_time', $dates)
		->get()
		->countBy(fn ($item) => $item->in_time->format('Y-m-d'));
	;
}

That should give you a collection whose contents have this structure:

[
		'2022-10-21' => 5,
		'2022-10-20' => 2,
		…
]

So it won’t be numerically indexed, but on the other hand, you will get the actual dates as part of the array. You won’t get any empty rows (i.e., dates with no attendances), but since I’m assuming you’re already using your $dates array outside the function, you can easily get around that:

// Controller
$dates = [ \ array of dates here // ];
$absentresult = attendanceData($dates, [4]);

// View
@foreach ($dates as $date)
	<tr>
		<td>{{ $date }}</td>
		<td>{{ $absentresult[$date] ?? '0' }}</td>
	</tr>
@endforeach

1 like

Please or to participate in this conversation.