query data in four levels tables by laravel eloquent

Question

I have this SQL structure to query schools and the number of student of male gender, I am asking for help for converting it to laravel eloquent

SELECT *
FROM schools && count(students has(gender == 'male'))
JOIN grades ON (grades.schools = schools.school_id)
JOIN streams ON (stream.schools = schools.school_id)
JOIN students ON (student.schools = schools.school_id)

this is what i did in the schemas school schema

 Schema::create('schools', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('educationLevel');
            $table->foreignId('ward_id')
            ->constrained('wards')
            ->onUpdate('cascade');
            $table->timestamps();
        });

grade

Schema::create('grades', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->foreignId('school_id')->constrained('schools')
            ->onDelete('cascade');
    $table->timestamps();});

stream

 Schema::create('streams', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->foreignId('grade_id')
            ->constrained('grades')
            ->onDelete('cascade');
            $table->timestamps();
    });

student

Schema::create('students', function (Blueprint $table) {
           $table->id();
            $table->string('student_name');
            $table->string('gender');
            $table->foreignId('stream_id')
            ->constrained('streams')
            ->onDelete('cascade');
            $table->timestamps();
        });

this is what i tried before in school controller

$schools = School::select(['name'])->withCount('students')->where('students', function($query){
    $query->where('gender', 'male');
})
->get();

in school model i did this below

 public function grades()
    {
        return $this->hasMany(Grade::class);
    }

  
    public function students(){
        return $this->hasManyThrough(Student::class, Stream::class, Grade::class);
    }

the relationship of this models is one to many like below school->has->grade->has->stream->student(gender = male or female)

Answer 1

You can leverage addSelect to get the desired output

School::query()
  ->addSelect([
      /** Total no of students in school */
      'count_students' => Student::selectRaw('count(*)')
          ->whereIn(
            'stream_id', 
            Stream::select('id')->whereIn(
              'grade_id',
              Grade::select('id')->whereColumn('school_id', 'schools.id')
            )
          ),
      /** Total no of "gender = male" students in school */
      'count_male' => Student::selectRaw('count(*)')
          ->whereRaw('gender = "male"')
          ->whereIn(
            'stream_id', 
            Stream::select('id')->whereIn(
              'grade_id',
              Grade::select('id')->whereColumn('school_id', 'schools.id')
            )
          ),
      /** Total no of "gender = female" students in school */
      'count_female' => Student::selectRaw('count(*)')
          ->whereRaw('gender = "female"')
          ->whereIn(
            'stream_id', 
            Stream::select('id')->whereIn(
              'grade_id',
              Grade::select('id')->whereColumn('school_id', 'schools.id')
            )
          ),
      /** Total no of "gender = other" students in school */
      'count_other' => Student::selectRaw('count(*)')
          ->whereRaw('gender = "other"')
          ->whereIn(
            'stream_id', 
            Stream::select('id')->whereIn(
              'grade_id',
              Grade::select('id')->whereColumn('school_id', 'schools.id')
            )
          )
  ])->get();