Laravel中如何基于计算列estimated_value进行筛选查询？

背景 #

开发Laravel项目时，需要为SalesLead模型的每条记录通过probability与value列相乘计算出estimated_value，并基于该计算列筛选记录。已通过全局范围添加计算列：

全局范围代码 #

class EstimatedValueScope implements Scope
{
    public function apply(Builder $builder, Model $model): void
    {
        $builder->addSelect([
            '*',
            \DB::raw('COALESCE(probability, 0) * COALESCE(value, 0) * 0.01 AS estimated_value'),
        ]);
    }
}

模型应用全局范围 #

class SalesLead extends Model
{
    protected static function booted()
    {
        static::addGlobalScope(new EstimatedValueScope);
    }
}

筛选尝试及错误 #

执行以下筛选代码时：

$salesLeads = SalesLead::where('estimated_value', '>', 1000000)->get();

出现SQL错误：

SQLSTATE[42703]: Undefined column: 7 ERROR:  column "estimated_value" does not exist
LINE 1: ...t count(*) as aggregate from "sales_leads" where ("estimated...
                                                         ^

需求限制 #

必须使用Eloquent的where方法筛选estimated_value（像操作普通列一样），避免使用whereRaw或DB::Raw。

问题解答 #

1. 如何正确基于计算列estimated_value进行筛选查询？ #

SQL中WHERE子句的执行顺序早于SELECT，因此无法直接引用SELECT里定义的别名。要实现用Eloquent原生where操作计算列，推荐用子查询包裹的方案：

修改全局范围的逻辑，先构建包含计算列的子查询，再让主查询从子查询中读取数据，此时WHERE就能正常引用estimated_value：

class EstimatedValueScope implements Scope
{
    public function apply(Builder $builder, Model $model): void
    {
        // 构建包含计算列的子查询
        $subQuery = $model->newQuery()->select([
            '*',
            \DB::raw('COALESCE(probability, 0) * COALESCE(value, 0) * 0.01 AS estimated_value'),
        ])->toBase();
        
        // 主查询从子查询获取数据，同时继承子查询的绑定参数
        $builder->from(\DB::raw("({$subQuery->toSql()}) as sales_leads"))
                ->mergeBindings($subQuery);
    }
}

修改后，你就能直接用SalesLead::where('estimated_value', '>', 1000000)->get()完成筛选，完全符合需求。

如果是小数据集，也可以用集合筛选的方式：先拉取所有带计算列的记录，再用集合的filter方法筛选，但这种方式不适合大数据量：

$allLeads = SalesLead::all();
$filteredLeads = $allLeads->filter(fn($lead) => $lead->estimated_value > 1000000);

2. 使用全局范围实现此需求是否合适？若不合适，最优方案是什么？ #

全局范围的适用性分场景：

• 合适场景：如果所有SalesLead的查询都需要包含estimated_value，全局范围能避免重复代码，是合理选择。
• 不合适场景：如果部分查询不需要这个计算列，全局范围会强制添加，增加不必要的计算开销。

最优替代方案：本地作用域（Local Scope）

定义本地作用域，让需要计算列的查询主动调用，灵活性更高：

class SalesLead extends Model
{
    // 添加计算列的本地作用域
    public function scopeWithEstimatedValue($query)
    {
        return $query->addSelect([
            '*',
            \DB::raw('COALESCE(probability, 0) * COALESCE(value, 0) * 0.01 AS estimated_value'),
        ]);
    }
    
    // 封装筛选计算列的作用域
    public function scopeWhereEstimatedValue($query, $operator, $value)
    {
        $subQuery = $this->newQuery()->withEstimatedValue()->toBase();
        return $query->from(\DB::raw("({$subQuery->toSql()}) as sales_leads"))
                    ->mergeBindings($subQuery)
                    ->where('estimated_value', $operator, $value);
    }
}

使用方式：

// 需要计算列时主动调用
$leads = SalesLead::withEstimatedValue()->get();
// 直接筛选计算列
$filteredLeads = SalesLead::whereEstimatedValue('>', 1000000)->get();

3. 如何确保estimated_value列被包含在查询中并正确用于筛选？ #

要同时满足“包含计算列”和“可用于筛选”，需做到三点：

1. 让计算列处于WHERE可访问的范围：通过子查询/CTE的方式（如问题1的方案），避免仅在SELECT中定义别名。
2. 避免重复添加计算列：在作用域中检查是否已添加该计算列，防止重复定义：

public function scopeWithEstimatedValue($query)
{
    $selects = $query->getQuery()->columns;
    $hasEstimatedValue = collect($selects)->contains(fn($col) => 
        is_string($col) && $col === 'estimated_value' || 
        $col instanceof Expression && str_contains($col->getValue(), 'AS estimated_value')
    );
    
    if (!$hasEstimatedValue) {
        $query->addSelect([
            '*',
            \DB::raw('COALESCE(probability, 0) * COALESCE(value, 0) * 0.01 AS estimated_value'),
        ]);
    }
    return $query;
}

3. 确保模型实例能读取计算列：给模型添加访问器，即使查询未返回该列，也能实时计算（可选）：

class SalesLead extends Model
{
    public function getEstimatedValueAttribute($value)
    {
        if (is_null($value)) {
            return (float)($this->probability ?? 0) * (float)($this->value ?? 0) * 0.01;
        }
        return (float)$value;
    }
}

内容的提问来源于stack exchange，提问作者Mostafa Lotfi