Laravel: query with a different where condition in a different row

Question

I have a classrooms table with a "quota" column that has different values. In one class, there are many students. How to display classrooms data with where condition which total students per row < "quota" ? Here's the table : .

Code :

Classroom::with('subject.teacher')->with('students')->whereHas('subject', fn ($query) => $query->where('grade', $grade))->withCount('students')->having('students_count', '<', 'quota');

when I use this code the result is empty

when "having" is removed this is the result :

The desired result only displays 3 classroom

Answer 1

You can use the has and a DB::raw.

// Simplified version
Classroom::has('students', '<=', DB::raw('classrooms.quota'))->get();

Answer 2

The withCount function appends an additional select column to your query. It does not use GROUP BY which is typically what is used in conjunction with having.

You can't actually filter by subqueries that are added in the select. However fortunately Laravel allows you to select rows based on how many related models they have using has. This query is also added as a subquery, but within the where clauses so you can also use column names within it like below:

Classroom::with('subject.teacher')
    ->with('students')
    ->whereHas('subject', fn ($query) => $query->where('grade', $grade))
    ->has('students', '<', \DB::raw('quota')) 
    ->withCount('students');