Laravel eloquent Group by belongsTo relationship-CodePudding

I have two models: Student and StudentArchive as below:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;


class Student extends Model
{

    protected $fillable = ['school_id', 'first_name', 'second_name', 'admin_number', 'archival_status'];

    public function student_archives(): HasMany
    {
        return $this->hasMany(StudentArchive::class);
    }
}

StudentArchive:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class StudentArchive extends Model{

    protected $casts = ['student_id' => 'int'];

    protected $fillable = ['student_id', 'class_of'];

    public function student(): BelongsTo{
        
        return $this->belongsTo(Student::class);
    }
}

I have the Student Model with the student details. Once a student graduates, I add their student_id to the StudentArchive model and the year of graduation.

I would want to get the count of students in the StudentArchive model per year hence groupby class_of which is YEAR eg 2022, 2021 e.t.c.

I have tried as below but the count is wrong, it gets one count for all. Anyone?

 StudentArchive::withCount(['student' => function($query) {
        return $query->where('school_id', Auth::user()->school_id)
        ->where('archival_status', true);  }])
        ->groupBy('class_of')
        ->get();

CodePudding user response：

You have a many to one relationship from StudentArchive to Student, therefore, the count does everything based on student_id and your query would work if it was a One to Many Relationship. In order to retrieve the count of students based on year, you don't have to do it with the relation at all. You can retrieve it directly by querying StudentArchive table only, and if you have where conditions to be checked for Student relations, you can use whereHas or whereExists.

Something like:

StudentArchive::whereHas('student', function ($q) { 
        $q->where('school_id', Auth::user()->school_id)
            ->where('archival_status', true); 
        }
    )
    ->select('class_of', DB::raw('count(*) as count_students'))
    ->groupBy('class_of')
    ->get();