Блог

У нас возникли проблемы с определенным запросом к базе данных в Laravel. У кого-нибудь есть идея о том, как оптимизировать этот код для более быстрой загрузки страницы? У нас очень большая база данных, теперь загрузка страницы занимает около полуминуты.

     public function index($year, int $weekStart, int $weekEnd)
{
    $employees = Employee::has('houses')
        ->with(['collections' => function ($collections) use ($year, $weekEnd) {
            $collections->where('year', '=', $year)
                ->where('week', '<=', $weekEnd)
                ->orWhere('year', '<', $year);
        }])
        ->get(['id', 'identifier', 'firstname', 'insertion', 'lastname']);

    foreach ($employees as $employee) {
        $weeks = [];

        for ($week = $weekStart; $week <= $weekEnd; $week  ) {
            $weeks[$week] = ['rent' => 0, 'addition' => 0, 'balance' => 0, 'week_balance' => 0];

            $employeeCollectionsInWeek = $employee->collections
                ->where('year', $year)
                ->where('week', $week);

            $employeeRentInWeek = $employeeCollectionsInWeek
                ->where('type', 'rent');

            $employeeAdditionInWeek = $employeeCollectionsInWeek
                ->where('type', 'addition');

            $employeeCollectionsUpToWeek = $employee->collections
                ->filter(function ($collection) use ($year, $week) {
                    return ($collection->year == $year amp;amp; $collection->week <= $week) || $collection->year < $year;
                });

            if ($employeeRentInWeek->count()) {
                $weeks[$week]['rent'] = round($employeeRentInWeek->sum('amount'), 2);
            }

            if ($employeeAdditionInWeek->count()) {
                $weeks[$week]['addition'] = round($employeeAdditionInWeek->sum('amount'), 2);
            }

            if ($employeeCollectionsUpToWeek->count()) {
                $totalToBePaid = $employeeCollectionsUpToWeek->whereIn('type', ['rent', 'addition'])->sum('amount');
                $totalPaid = $employeeCollectionsUpToWeek->whereIn('type', ['payment_cash', 'payment_card', 'payment_transfer', 'payment_tikkie', 'remission'])->sum('amount');
                $weeks[$week]['balance'] = round($totalPaid - $totalToBePaid, 2);
            }

            if ($employeeCollectionsInWeek->count()) {
                $totalToBePaidInWeek = $employeeCollectionsInWeek->whereIn('type', ['rent', 'addition'])->sum('amount');
                $totalPaidInWeek = $employeeCollectionsInWeek->whereIn('type', ['payment_cash', 'payment_card', 'payment_transfer', 'payment_tikkie', 'remission'])->sum('amount');
                $weeks[$week]['week_balance'] = round($totalPaidInWeek - $totalToBePaidInWeek, 2);
            }
        }

        $employee['weeks'] = $weeks;
    }

    return $employees;
}
  

С уважением,

Tom van der Hout, Dylan Vollebregt, Dayton de Lange amp; Daan Jansen