Laravel中如何将最大值查询集成到Where条件以简化代码

需求是从approval_preferences表中获取符合条件的role_id，要求记录的level字段大于$this->approval_level，同时不等于当前公司下该字段的最大值。

现有可运行代码 #

目前的实现是先单独查询最大值，再用这个值过滤主查询：

$maxLevel = ApprovalPreference::where('company_id', $this->company_id)
    ->max('level'); // 想省略这一行，合并到主查询里
$role = ApprovalPreference::where('company_id', $this->company_id)
    ->where('level', '>', $this->approval_level)
    ->where('level', '<', $maxLevel)
    ->orderBy('level', 'asc')
    ->pluck('role_id')
    ->first();

尝试过程 #

一开始尝试用DB::raw写子查询，但直接写会报错：

->where('level', '<', DB::raw('select MAX(level) from approval_preferences')

给子查询加上外层括号后就能正常运行：

->where('level', '<', DB::raw('(select MAX(level) from approval_preferences)')

最终优化方案 #

使用whereRaw方法将最大值查询集成到主语句中，同时要注意加上company_id的条件，避免取到其他公司的最大值：

$role = ApprovalPreference::where('company_id', $this->company_id)
    ->where('level', '>', $this->approval_level)
    ->whereRaw("level < (SELECT MAX(level) from approval_preferences where company_id = {$this->company_id})")
    ->orderBy('level', 'asc')
    ->pluck('role_id')
    ->first();

安全优化提示 #

直接拼接变量存在SQL注入风险，更安全的写法是使用参数绑定：

$role = ApprovalPreference::where('company_id', $this->company_id)
    ->where('level', '>', $this->approval_level)
    ->whereRaw("level < (SELECT MAX(level) from approval_preferences where company_id = ?)", [$this->company_id])
    ->orderBy('level', 'asc')
    ->pluck('role_id')
    ->first();

内容的提问来源于stack exchange，提问作者Sabin Chacko