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

Can you share some model code and also columns that you are using — ManojKiran A, Nov 25 '21 at 10:53
My 4 Models/Tables are given above along with the column names. School, Class, Section and Student. — B.M., Nov 25 '21 at 11:01
yes, properly defined. for example, the Class table has school_id as the foreign key to the School table id column. The Section and Student tables also have relationships defined. — B.M., Nov 25 '21 at 11:18
Do you have already defined ```total_seats``` columns in any of the table ? — ManojKiran A, Nov 25 '21 at 11:19
The Section Table has the field no_of_seats. That is the total seat for a Section, and Sum of all such values of Sections under a Class will be the total seats for that class, And the sum of such totals of Classes for a school will be the total seats for a School. — B.M., Nov 25 '21 at 11:22
You may think of Sections as Class Rooms where one Class may be divided into many rooms having predefined seat capacity. — B.M., Nov 25 '21 at 11:25
I will use all those columns you have provided and receate it and post the answer shortly — ManojKiran A, Nov 25 '21 at 11:27

mpyw · Accepted Answer · 2021-12-02T01:53:17.397

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();

score 1 · Answer 2 · answered Nov 30 '21 at 15:31

This is not what you asked for as it uses Query Builder instead of Eloquent. I have not tested it as I have nothing to test against currently but this should work -

use Illuminate\Support\Facades\DB;

$students_per_section = DB:table('students')
        ->select('section_id', DB::raw('COUNT(id) AS num_students'))
        ->groupBy('section_id')

$query = DB:table('schools')
        ->join('classes', 'schools'.'id', '=', 'classes.school_id')
        ->join('sections', 'classes.id', '=', 'sections.class_id')
        ->leftJoinSub($students_per_section, 'students_per_section', function($join) {
            $join->on('sections.id', '=', 'students_per_section.section_id')
        });

if ($school_id) {
    $query
        ->select('classes.class_name', DB::raw('SUM(no_of_seats) AS total_seats'), DB::raw('SUM(students_per_section.num_students) AS student_registered'))
        ->where('schools.id', '=', $school_id)
        ->groupBy('classes.class_name')
} else {
    $query
        ->select('schools.school_name', DB::raw('SUM(no_of_seats) AS total_seats'), DB::raw('SUM(students_per_section.num_students) AS student_registered'))
        ->groupBy('schools.school_name')
}

$stats = $query->get();

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

2 Answers2

Definition

Example