通過laraveleloquent查詢四級表中的資料

我有這個 SQL 結構來查詢學校和男性學生的數量，我正在尋求幫助以將其轉換為 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)

這就是我在模式學校模式中所做的

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

年級

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

溪流

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

學生

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

這是我之前在學校控制器中嘗試過的

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

在學校模型中，我在下面做了這個

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

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

這個模型的關系是一對多的，就像下面的學校->有->年級->有->流->學生（性別=男性或女性）

uj5u.com熱心網友回復：

您可以利用addSelect來獲得所需的輸出

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

uj5u.com熱心網友回復：

你需要一些接近這個的東西：

$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'
            ]);

要獲得資料的數量，您只需使用 count 方法進行計算。

$schoolCount = count($schools);

確保您的表名和列名是適當的，我發現您的模型和表名有很多不一致之處，列也是如此。此外，使用 join 您可能會遇到諸如具有相同名稱的列之類的問題。確保在 get 方法中單獨指定它們。例如：

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

標籤：拉拉维尔 雄辩 where子句

上一篇：是否有一次性鏈接生成Laravel？

下一篇：如何在laravel中洗掉陣列單元