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

user_16256959 0
Viewed 125

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 : 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 : result

The desired result only displays 3 classroom

laravel-8 eloquent laravel
2 Answers

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

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

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');
user_4878130
Related