How to use alias in where clause laravel elequent with postgres

This is my query

Student::query()
    ->addSelect(['presentCount' =>
        Score::query()
            ->selectRaw('count(*)')
            ->whereColumn('student_id', '=', 'students.id')
            ->where('created_at', '>', getBeginningOfThisYear())
            ->where('presence', '=', 1)
    ])->where('presentCount', '>=', 5)->get()

the raw SQL is

select "students".*, (select count(*) from "scores" where "student_id" = "students"."id" and "created_at" > ? and "presence" = ? and "scores"."deleted_at" is null) as "presentCount" from "students" where "presentCount" >= ? and "students"."deleted_at" is null

the problem is presentCount is an alias and it can not be used in where clause, however if I can change the SQL to be like:

select * from (select "students".*, (select count(*) from "scores" where "student_id" = "students"."id" and "created_at" > ? and "presence" = ? and "scores"."deleted_at" is null) as "presentCount" from "students" ) as students where "students"."presentCount" >= ? and "students"."deleted_at" is null

The question is how can I transform the query using query builder not raw SQL?

Sinnbeck

3 years ago

Level 102

Im curious why it adds the table name in the first place?

If I do User::where('foo', '>', 3)->toSql(); it isnt prefixed. Are you changing this with code or a package?

Anywhat. Why dont just let laravel handle it? Something like (assuming a scores relationship)

Student::query()
    ->withCount(['scores as presentCount' => fn($query) => 
            $query
            ->where('created_at', '>', getBeginningOfThisYear())
            ->where('presence', '=', 1);
    ])->where('presentCount', '>=', 5)->get()

osama_abdullah

3 years ago

Level 14

@Sinnbeck

your query generates the same SQL statement:

Student::query()
    ->withCount(['scores as presentCount' => fn($query) => 
            $query
            ->where('created_at', '>', getBeginningOfThisYear())
            ->where('presence', '=', 1);
    ])->where('presentCount', '>=', 5)->toSql()
// will be evaluated to:
select "students".*, (select count(*) from "scores" where "students"."id" = "scores"."student_id" and "created_at" > ? and "presence" = ? and "scores"."deleted_at" is null) as "presentCount" from "students" where "presentCount" >= ? and "students"."deleted_at" is null

and the error message is:

SQLSTATE[42703]: Undefined column: 7 ERROR:  column \"presentCount\" does not exist

because in SQL the where clause is executing before the select statement, SQL runs by this order

FROM
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY

the only solution is to put it in as a subquery after FROM keyword then you can use it in the WHERE clause as it's getting evaluated before.

osama_abdullah

3 years ago

Best Answer

Level 14

@Sinnbeck

I found a solution:

Student::query()->from(fn($q) => $q->from('students')->addSelect(['presentCount' =>
            Score::query()
                ->selectRaw('count(*)')
                ->whereColumn('student_id', '=', 'students.id')
                ->where('created_at', '>', getBeginningOfThisYear())
                ->where('presence', '=', 1)
        ]), 'students')->where('presentCount', '>', 5)->get()

will generate:

select * from (select "students".*, (select count(*) from "scores" where "student_id" = "students"."id" and "created_at" > ? and "presence" = ? and "scores"."deleted_at" is null) as "presentCount" from "students") as "students" where "presentCount" > ? and "students"."deleted_at" is null

Thank you for your help

1 like

Please or to participate in this conversation.