php - 使用 Laravel 计算平均值，按类别和日期范围分组，并在单个页面上显示输出

Question

我有一个看起来像这样的表:

我希望能够查询此表并根据按主题 和日期 分组显示结果。日期将满足一个范围(在 x 和 y 之间)。

我希望输出这样的东西:

Term 1 - Art - (average of all asssessed_level for x range)

Term 2 - Art - (average of all asssessed_level for y range)

Term 3 - Art - (average of all asssessed_level for z range)

还有这个:

Term 1 - Math - (average of all asssessed_level for x range)

Term 2 - Math - (average of all asssessed_level for y range)

Term 3 - Math - (average of all asssessed_level for z range)

(等)

如果某个日期范围没有 assessed_level，那么我仍然需要它来返回一个主题，该主题可能是该地点的“N/A”。

最终，我想要一个如下所示的数据表:

(“E”将是平均 assessed_level 所在的位置，YEAR 列将是一个平均结果并忽略日期范围)

这是我编写的代码，但它没有提供我需要的结果:

for($b=0; $b < $assessed_subjects->count(); $b++) {
    $assessment_strengths[] = DB::table('assessment_data')
        ->join('subjects', 'assessment_data.subject_id', '=', 'subjects.id')
        ->join('units', 'assessment_data.unit_id', '=', 'units.id')
        ->join('outcomes', 'assessment_data.outcome_id', '=', 'outcomes.id')
        ->join('assessments', 'assessment_data.assessment_id', '=', 'assessments.id')
        ->select('subjects.short_name as subject_name', 'units.name as unit_name', 'outcomes.name as outcome_name', 'assessments.assessment_name as assessment_name', 'assessment_data.*')
        ->where('assessment_data.user_id', 28)
        ->where('assessment_data.student_id', $student_id)
        ->where('assessment_data.subject_id', $assessed_subjects[$b]->id)
        ->whereBetween('assessment_data.created_at', [$current_term->term_start, $current_term->term_end])
        ->orderBy('assessment_data.assessed_level', 'desc')
        ->get();
}

这是我在 dd() 查询时得到的输出:

array:8 [▼
  0 => Collection {#371 ▼
    #items: []
  }
  1 => Collection {#386 ▼
    #items: []
  }
  2 => Collection {#392 ▼
    #items: array:1 [▼
      0 => {#390 ▼
        +"subject_name": "Math"
        +"unit_name": "Rocks and Minerals"
        +"outcome_name": "Analyze how positive health habits can be supported by a variety of approaches to health practices and treatments"
        +"assessment_name": "The First Assessment"
        +"id": 36
        +"user_id": 28
        +"room_id": 1
        +"assessment_id": 1
        +"student_id": 6
        +"subject_id": 19
        +"unit_id": 188
        +"outcome_id": 476
        +"assessed_level": 4.0
        +"created_at": "2018-03-29 10:04:42"
        +"updated_at": "2018-03-29 10:04:42"
      }
    ]
  }
  3 => Collection {#399 ▼
    #items: array:1 [▼
      0 => {#397 ▼
        +"subject_name": "Social"
        +"unit_name": "Animals"
        +"outcome_name": "Demonstrate and explain the effect of adding zero to, or subtracting zero from, any number."
        +"assessment_name": "The First Assessment"
        +"id": 48
        +"user_id": 28
        +"room_id": 1
        +"assessment_id": 1
        +"student_id": 6
        +"subject_id": 25
        +"unit_id": 122
        +"outcome_id": 27
        +"assessed_level": 4.0
        +"created_at": "2018-01-01 10:04:42"
        +"updated_at": "2018-03-29 10:04:42"
      }
    ]
  }
  4 => Collection {#406 ▼
    #items: []
  }
  5 => Collection {#412 ▼
    #items: []
  }
  6 => Collection {#418 ▼
    #items: array:1 [▼
      0 => {#416 ▼
        +"subject_name": "ELA"
        +"unit_name": "Explore - Clarify and Extend"
        +"outcome_name": "State, orally in their own words, that in French the indefinite article is not used when identifying one’s profession (e.g., m. brown est dentiste.)"
        +"assessment_name": "The First Assessment"
        +"id": 18
        +"user_id": 28
        +"room_id": 1
        +"assessment_id": 1
        +"student_id": 6
        +"subject_id": 6
        +"unit_id": 25
        +"outcome_id": 3000
        +"assessed_level": 4.0
        +"created_at": "2018-03-29 10:04:42"
        +"updated_at": "2018-03-29 10:04:42"
      }
    ]
  }
  7 => Collection {#425 ▼
    #items: []
  }
]

如您所见，我想将一些数据推送到空值(0、1、4、5、7)。我查看了 IFNULL 和 COALESCE 作为提供回退的一种方式，但这在这种情况下没有帮助。有没有办法将值(或类似的东西)附加到查询结果中，即使它们是空的？

我的想法是，如果我可以将输出的每条记录定义为属于特定术语，我将更接近于拥有我需要的东西。

Answer 1

我会建议这样的方法:

$result = [];
foreach($assessed_subjects as $subject) {
    $query = DB::table('assessment_data')
        ->select(DB::raw('avg(`assessed_level`) as `avg`'))
        ->where('user_id', 28)
        ->where('student_id', $student_id)
        ->where('subject_id', $subject->subject_id);
    foreach($user_terms as $term) {
        $result[$subject->subject_id][$term->id] = (clone $query)
            ->whereBetween('created_at', [$term->term_start, $term->term_end])
            ->first()->avg;
    }
    $result[$subject->subject_id]['year'] = (clone $query)
        ->whereBetween('created_at', [$user_terms->first()->term_start, $user_terms->last()->term_end])
        ->first()->avg;
}

这会为您提供按主题和术语分组的平均水平。如果没有值，则为 NULL。

或者查询更少的替代解决方案:

$result = [];
$query = DB::table('assessment_data')
    ->select('subject_id', DB::raw('avg(`assessed_level`) as `avg`'))
    ->where('user_id', 28)
    ->where('student_id', $student_id)
    ->whereIn('subject_id', $assessed_subjects->pluck('subject_id'))
    ->groupBy('subject_id');
foreach($user_terms as $term) {
    $rows = (clone $query)
        ->whereBetween('created_at', [$term->term_start, $term->term_end])
        ->get();
    foreach($rows as $row) {
        $result[$row->subject_id][$term->id] = $row->avg;
    }
}
$rows = (clone $query)
    ->whereBetween('created_at', [$user_terms->first()->term_start, $user_terms->last()->term_end])
    ->get();
foreach($rows as $row) {
    $result[$row->subject_id]['year'] = $row->avg;
}

唯一的缺点是它不会为结果中的缺失平均值添加 NULL 值。