Laravel Question posted in
The official Laravel Documentation can be found here.

How to Filter and Query Based on a Computed Column in Laravel?

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:

  1. How can I properly filter and query based on this computed estimated_value column?
  2. Is using a global scope for this purpose the right approach? If not, what would be the best way to handle this?
  3. How can I ensure that the estimated_value column is included in the query and used correctly for filtering?
Tags:

2

Answers


  1. 0

    I believe that we have to use having instead of where for aggregated columns like estimated_value:

    $salesLeads = SalesLead::having('estimated_value', '>', 1000000)->get();
    Login or Signup to reply.
  2. 0

    Filtering and querying based on a computed column in Eloquent requires a bit of care because the computed column doesn’t exist in the database, so you can’t directly use Eloquent’s where method as you would with a regular column. Here’s how you can approach this:

    Hope it will be useful for you:

    Using a Global Scope with a Subquery:

    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'),
        ]);
    }
}

    Querying with a Subquery for Filtering:

    $salesLeads = SalesLead::where(function($query) {
    $query->select(DB::raw('COALESCE(probability, 0) * COALESCE(value, 0) * 0.01'))
          ->from('sales_leads')
          ->whereColumn('sales_leads.id', 'id')
          ->limit(1);
}, '>', 1000000)->get();

    An Alternative: Use an Accessor:

    class SalesLead extends Model
{
    protected $appends = ['estimated_value'];

    public function getEstimatedValueAttribute()
    {
        return ($this->probability ?? 0) * ($this->value ?? 0) * 0.01;
    }
}

    You can then filter like this, although this will involve fetching all the records and filtering in memory:

    $salesLeads = SalesLead::all()->filter(function($lead) {
    return $lead->estimated_value > 1000000;
});

    Using whereRaw:

    $salesLeads = SalesLead::whereRaw('(COALESCE(probability, 0) * COALESCE(value, 0) * 0.01) > ?', [1000000])->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search