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)

CodePudding user response：

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

CodePudding user response：

You would need something approaching this:

$schools  = School::Join('grades', 'grades.schools', '=', 'schools.school_id')
            ->Join('stream', 'stream.schools', '=', 'schools.school_id')
            ->Join('student', 'student.schools', '=', 'schools.school_id')
            ->where('gender', "male")
            ->get([
                'name','yourdata'
            ]);

To get the count of data, you simply calculate with count method.

$schoolCount = count($schools);

Make sure your table and column names are appropriate, I am seeing a lot of inconsistencies in your model and table names, same for columns. also, using join you might run into issues like columns with same name. make sure to specify them separately in the get method.Like:

->get([
                'id', 'student.schools as stud', 'stream.name as streamName', 
            ]);