Group by and Sum of One-To-Many relation tables in Eloquent

Question

I have a requirement. My DB has tables like the following. The tables have OneToMany (1-n) parent-child relation.

Table School (id, school_name)

Table Class (id, school_id, class_name)

Table Section (id, class_id, section_name, no_of_seats)

Table Student (id, section_id, student_name, ....)

When Some Student is registered, data is uploaded to the Student table. Now, I want to have a statistic like

| school_name | total_seats | student_registered |

and for a particular school

| class_name | total_seats | student_registered |

How to achieve this in Laravel/Eloquent

Thanks in Advance

Answer 1

Probably it works with:

• Counting/Summarizing HasMany relations
• Counting/Summarizing HasManyThrough relations
• Counting/Summarizing HasManyDeep relations

Definition

class Section extends Model
{
    public function students(): HasMany
    {
        return $this->hasMany(Student::class);
    }

    public function scopeWithRegisteredStudents(Builder $query): Builder
    {
        // Count HasMany relation
        return $query->withCount('students as students_registered');
    }
}

// The word "Class" is reserved, so we need to use "SchoolClass" instead
class SchoolClass extends Model
{
    protected $table = 'classes';

    public function sections(): HasMany
    {
        return $this->hasMany(Section::class, 'class_id');
    }

    public function students(): HasManyThrough
    {
        return $this->hasManyThrough(Student::class, Section::class, 'class_id');
    }

    public function scopeWithTotalSeats(Builder $query): Builder
    {
        // Summarize field from HasMany relation
        return $query->withSum('sections as total_seats', 'no_of_seat');
    }

    public function scopeWithRegisteredStudents(Builder $query): Builder
    {
        // Count HasManyThrough relation
        return $query->withCount('students as students_registered');
    }
}

class School extends Model
{
    public function classes(): HasMany
    {
        return $this->hasMany(SchoolClass::class);
    }

    public function sections(): HasMany
    {
        return $this->hasManyThrough(Section::class, SchoolClass::class, null, 'class_id');
    }

    public function students(): HasManyThrough
    {
        // https://github.com/staudenmeir/eloquent-has-many-deep
        return $this->hasManyDeep(Student::class, [SchoolClass::class, Section::class], ['school_id', 'class_id', 'section_id'], ['id', 'id', 'id']);
    }

    public function scopeWithTotalSeats(Builder $query): Builder
    {
        // Summarize field from HasManyThrough relation
        return $query->withSum('sections as total_seats', 'no_of_seat');
    }

    public function scopeWithRegisteredStudents(Builder $query): Builder
    {
        // Count HasManyDeep relation
        return $query->withCount('students as students_registered');
    }
}

Example

// Fetching simply
Section::query()
    ->withRegisteredStudents()
    ->get();
SchoolClass::query()
    ->withTotalSeats()
    ->withRegisteredStudents()
    ->get();
School::query()
    ->withTotalSeats()
    ->withRegisteredStudents()
    ->get();

// Fetching with nested relations
School::query()
    ->withTotalSeats()
    ->withRegisteredStudents()
    ->with(['classes' => function (HasMany $query) {
        return $query
            ->withTotalSeats()
            ->withRegisteredStudents();
    }])
    ->get();

If you use a static analyzer like PHPStan or Psalm, you can alternatively use scopes method to prevent errors.

School::query()
    ->scopes(['withTotalSeats', 'withRegisteredStudents'])
    ->get();