如何使用Laravel Eloquent实现一对多关联表的分组聚合统计？

1 先定义模型关联 #

首先在四个对应模型中声明层级关联，注意：PHP中Class为保留关键字，实际开发中建议将班级模型命名为SchoolClass避免语法错误，对应表名保持classes即可：

School 模型 #

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\HasManyThrough;

class School extends Model
{
    protected $fillable = ['school_name'];

    // 关联班级
    public function classes(): HasMany
    {
        return $this->hasMany(Class::class);
    }

    // 跨层级关联分班
    public function sections(): HasManyThrough
    {
        return $this->hasManyThrough(Section::class, Class::class);
    }

    // 跨层级关联学生
    public function students(): HasManyThrough
    {
        return $this->hasManyThrough(Student::class, Class::class)
            ->through(Section::class);
    }
}

Class 模型 #

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\HasManyThrough;

class Class extends Model
{
    protected $fillable = ['school_id', 'class_name'];

    // 关联学校
    public function school(): BelongsTo
    {
        return $this->belongsTo(School::class);
    }

    // 关联分班
    public function sections(): HasMany
    {
        return $this->hasMany(Section::class);
    }

    // 跨层级关联学生
    public function students(): HasManyThrough
    {
        return $this->hasManyThrough(Student::class, Section::class);
    }
}

Section 模型 #

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Section extends Model
{
    protected $fillable = ['class_id', 'section_name', 'no_of_seats'];

    // 关联班级
    public function class(): BelongsTo
    {
        return $this->belongsTo(Class::class);
    }

    // 关联学生
    public function students(): HasMany
    {
        return $this->hasMany(Student::class);
    }
}

Student 模型 #

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Student extends Model
{
    protected $fillable = ['section_id', 'student_name'];

    // 关联分班
    public function section(): BelongsTo
    {
        return $this->belongsTo(Section::class);
    }
}

2 全量学校维度统计实现 #

直接使用Eloquent的withSum和withCount方法做聚合查询，一次查询拿到所有统计值，返回字段完全匹配要求：

$schoolStats = School::query()
    ->select('school_name')
    // 统计所有分班的座位总和
    ->withSum('sections as total_seats', 'no_of_seats')
    // 统计所有注册学生数量
    ->withCount('students as student_registered')
    ->get();

3 指定学校下的班级维度统计实现 #

传入指定学校ID即可筛选对应班级的统计数据，返回字段完全匹配要求：

// $schoolId 为指定要查询的学校ID
$classStats = Class::query()
    ->where('school_id', $schoolId)
    ->select('class_name')
    // 统计当前班级下所有分班的座位总和
    ->withSum('sections as total_seats', 'no_of_seats')
    // 统计当前班级下所有注册学生数量
    ->withCount('students as student_registered')
    ->get();

可选：高性能JOIN实现（避免关联层级过多的性能问题） #

如果数据量较大，不想用跨层级关联，可直接用join写法，性能更优：

学校维度统计JOIN版本 #

$schoolStats = School::query()
    ->select('schools.school_name')
    ->selectRaw('SUM(sections.no_of_seats) as total_seats')
    ->selectRaw('COUNT(students.id) as student_registered')
    ->leftJoin('classes', 'schools.id', '=', 'classes.school_id')
    ->leftJoin('sections', 'classes.id', '=', 'sections.class_id')
    ->leftJoin('students', 'sections.id', '=', 'students.section_id')
    ->groupBy('schools.id', 'schools.school_name')
    ->get();

班级维度统计JOIN版本 #

$classStats = Class::query()
    ->where('classes.school_id', $schoolId)
    ->select('classes.class_name')
    ->selectRaw('SUM(sections.no_of_seats) as total_seats')
    ->selectRaw('COUNT(students.id) as student_registered')
    ->leftJoin('sections', 'classes.id', '=', 'sections.class_id')
    ->leftJoin('students', 'sections.id', '=', 'students.section_id')
    ->groupBy('classes.id', 'classes.class_name')
    ->get();

内容的提问来源于stack exchange，提问作者B.M.