如何在Laravel Eloquent（PostgreSQL）中用查询构建器实现WHERE子句使用别名？

我原本的Laravel查询代码如下：

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

对应的原生SQL为：

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

这里的问题是presentCount是查询结果的别名，无法直接在WHERE子句中使用。我知道可以将SQL改写为嵌套子查询的形式解决：

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

请问如何不使用原生SQL，仅通过Laravel查询构建器实现上述改写后的查询？

方法一：嵌套子查询（fromSub） #

先构建包含presentCount字段的子查询，再将其作为外层查询的数据源，在外层对别名字段进行筛选：

// 构建子查询：包含学生基础信息和presentCount统计
$subQuery = Student::query()
    ->addSelect(['presentCount' =>
        Score::query()
            ->selectRaw('count(*)')
            ->whereColumn('student_id', '=', 'students.id')
            ->where('created_at', '>', getBeginningOfThisYear())
            ->where('presence', '=', 1)
    ]);

// 外层查询基于子查询结果过滤
$students = DB::query()
    ->fromSub($subQuery, 'students')
    ->where('presentCount', '>=', 5)
    ->whereNull('students.deleted_at')
    ->get();

这段代码完全通过Laravel查询构建器生成你需要的嵌套SQL，无需手写原生语句。

方法二：使用HAVING子句筛选 #

由于presentCount是计算生成的字段，也可以直接用HAVING子句替代WHERE来筛选，写法更简洁：

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

Laravel支持对查询中定义的别名使用HAVING，这种写法会生成等价的筛选逻辑，同样能解决别名无法在WHERE中使用的问题。

内容的提问来源于stack exchange，提问作者osama Abdullah