I'm working on a Laravel project where I need to calculate an estimated_value for each record in my SalesLead model by multiplying the probability and value columns. I want to filter records based on this computed column.
I used a global scope to add the computed column like this:
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'),
]);
}
}
And applied it to my model:
class SalesLead extends Model
{
protected static function booted()
{
static::addGlobalScope(new EstimatedValueScope);
}
}
I want to filter records using this computed column, for example:
$salesLeads = SalesLead::where('estimated_value', '>', 1000000)->get();
But I get an SQL error:
SQLSTATE[42703]: Undefined column: 7 ERROR: column "estimated_value" does not exist
LINE 1: ...t count(*) as aggregate from "sales_leads" where ("estimated...
^
Note: I need to use Eloquent's where method to filter by estimated_value, just like with any other column, and avoid using whereRaw or DB:Raw.
Questions:
- How can I properly filter and query based on this computed
estimated_valuecolumn? - Is using a global scope for this purpose the right approach? If not, what would be the best way to handle this?
- How can I ensure that the
estimated_valuecolumn is included in the query and used correctly for filtering?