I have a one to one relationship between Registration and Evaluation. A registration can have an evaluation. A registration has student_id, course_no, course_name, instructor_name and I want to get all distinct courses with the evaluation count.
Ex.
| id | student_id | course_no | course_name | instructor_name |
| -- | --------- | ---------- | ------------ | --------------- |
| 1 | 1 |CSC 123 A | Intro | John Doe |
| 2 | 1 | CSC 098 C | Data | Kate Paul |
| 3 | 2 | CSC 123 A | Intro | John Doe |
And each of these has an evaluation. So I want the end result to look like:
| course_no | course_name | instructor_name | evaluation_count |
| ---------- | ------------ | --------------- | ---------------- |
| CSC 123 A | Intro. | John Doe | 2 |
| CSC 098 C | Data | Kate Paul | 1 |
So far I have the below code:
Registration::select('id', 'course_no', 'course_name', 'instructor_name', 'semester', 'section_type')
->withCount('evaluation')->get();
With this I get repeated courses. P.S. I tried group by too and it does the same.
Duplicates are because the query is performed on Registration model which (i assume) is for many courses, one course has many registrations. Keeping the same approach i think the best way is this:
Registration::withCount('evaluation', function($query) {
$query->select(DB::raw('count(distinct(course_no))'));
})->get();
I don't think withCount is gonna work in this scenario.
I reckon you can join the two tables together and group by the course_no:
Registration::selectRaw('registrations.course_no, COUNT(evaluations.id) as evaluations_count')
->join('evaluations', 'registrations.id', '=', 'evaluations.registration_id')
->groupBy('registrations.course_no')
->get();
Alternatively, as you said, registrations are one-on-one with evaluations, then simply counting registration ids is good enough:
Registration::selectRaw('registrations.course_no, COUNT(id) as evaluations_count')
->get();
Please or to participate in this conversation.