时间戳

首页 > 解决方案 > 如何优化此方法并避免 foreach 循环

php - 如何优化此方法并避免 foreach 循环

问题描述

我的 Laravel 应用程序中有以下模型：课程、活动、学生、注册、出勤。

我想检索至少有一名学生的出勤记录少于课程中预期事件总数的课程列表。例如，如果一门课程有 10 个事件，我需要获取所有课程，其中至少有一个学生的出勤记录少于这些事件的 10 个预期出勤记录。

作为临时解决方案，我在 Course 模型中使用了嵌套的 foreach 循环：

public function getEventsWithExpectedAttendanceAttribute()
{
    return $this->events()->where(function($query) {
        $query->where('exempt_attendance', '!=', true);
        $query->where('exempt_attendance', '!=', 1);
        $query->orWhereNull('exempt_attendance');
    })->where('start', '<', Carbon::now(env('COURSES_TIMEZONE'))->toDateTimeString())->get();
}

public function getMissingAttendanceAttribute()
{
    $eventsWithMissingAttendanceCount = 0;

    // loop through every event supposed to have attendance
    foreach ($this->events_with_expected_attendance as $event)
    {
        // loop through every student
        foreach ($this->enrollments as $enrollment)
        {
            // if the student has no attendance record for this event
            if (Attendance::where('student_id', $enrollment->student_id)->where('event_id', $event->id)->count() == 0)
            {
    // count one and break loop
            $eventsWithMissingAttendanceCount++;
            break;
            }
        }
    }

    return $eventsWithMissingAttendanceCount;
}

但这在性能方面确实不漂亮，而且效率也非常低下。但是，到目前为止，我一直无法找到更好的解决方案。任何帮助将不胜感激！

更新：这是数据库模式

CREATE TABLE `courses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`));

CREATE TABLE `events` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `course_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `enrollments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `student_id` int(10) unsigned NOT NULL,
  `course_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `students` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`));

CREATE TABLE `attendances` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `student_id` int(10) unsigned NOT NULL,
  `event_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`));

标签： phpmysqllaraveloptimizationeloquent

解决方案

您的方法具有二次 n^2 性能，因为查询数乘以事件和出勤数。

减少查询数量的第一步可能是从最内层循环中提取此查询

Attendance::where('student_id', $enrollment->student_id)->where('event_id', $event->id)->count() == 0

并提前获取出勤率

public function getMissingAttendanceAttribute()
{
    $eventsWithMissingAttendanceCount = 0;

    $eventsIDs = $this->events_with_expected_attendance->pluck('id');
    $studentIDs = $this->enrollments->pluck('student_id');

    // Collection of attendances
    $attendances = Attendance::whereIn('student_id', $studentIDs)
        ->whereIn('event_id', $eventsIDs)
        ->get();


    // loop through every event supposed to have attendance
    foreach ($this->events_with_expected_attendance as $event)
    {
        // loop through every student
        foreach ($this->enrollments as $enrollment)
        {
            $hasNotAttended = $attendances->where('student_id', $enrollment->student_id)
                ->where('event_id', $event->id)
                ->isEmpty();

            if ($hasNotAttended) {
                $eventsWithMissingAttendanceCount++;
                break;
            }
        }
    }

    return $eventsWithMissingAttendanceCount;
}

这应该会提高性能，因为查询的数量不会随着事件和注册的数量而扩展，而是保持不变。

例如，您可以提取$hasNotAttended到注册模型的方法。

public function getMissingAttendanceAttribute()
{
    $eventsWithMissingAttendanceCount = 0;

    $eventsIDs = $this->events_with_expected_attendance->pluck('id');
    $studentIDs = $this->enrollments->pluck('student_id');

    // Collection of attendances
    $attendances = Attendance::whereIn('student_id', $studentIDs)
        ->whereIn('event_id', $eventsIDs)
        ->get();


    // loop through every event supposed to have attendance
    foreach ($this->events_with_expected_attendance as $event)
    {
        // loop through every student
        foreach ($this->enrollments as $enrollment)
        {
            // pass preloaded attendances.
            // alternatively you could preload this relationship then no need to pass $attendances
            if ($enrollment->hasNotAttended($event->id, $attendances)) {
                $eventsWithMissingAttendanceCount++;
                break;
            }
        }
    }

    return $eventsWithMissingAttendanceCount;
}

然后，您可以通过提取以下内容来扩展$this->enrollments集合https://laravel.com/docs/6.x/collections#extending-collections 。

    if ($enrollment->hasNotAttended($event->id, $attendances)) {
        $eventsWithMissingAttendanceCount++;
        break;
    }

所以，它会被简化为

public function getMissingAttendanceAttribute()
{
    $eventsWithMissingAttendanceCount = 0;

    $eventsIDs = $this->events_with_expected_attendance->pluck('id');
    $studentIDs = $this->enrollments->pluck('student_id');

    // Collection of attendances
    $attendances = Attendance::whereIn('student_id', $studentIDs)
        ->whereIn('event_id', $eventsIDs)
        ->get();


    // loop through every event supposed to have attendance
    foreach ($this->events_with_expected_attendance as $event)
    {
        if ($this->enrollments->hasNotAttended($event->id, $attendances)) {
            $eventsWithMissingAttendanceCount++;
        }
    }

    return $eventsWithMissingAttendanceCount;
}

推荐阅读