CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3activerecord
Tintin81
Tintin81

Reputation: 10207

How to run where clause on a function rather than a database column?

In my Invoice model I am trying to fetch all outstanding invoices:

class Invoice < ActiveRecord::Base

  has_many :payments

  def self.outstanding
    where("outstanding_amount != 0")
  end

  private

  def outstanding_amount
    total - payments.to_a.sum(&:amount_in_cents)
  end

end

This doesn't work, however, because I don't have a database column outstanding_amount. Is there any way to run a where clause on a function rather than a database field?

Thanks for any help.

Upvotes: 0

Views: 98

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78513

You need to pass the expression as part of a having clause. I'll defer to RoR wizards for the active record syntax, but your final query should amount to something like:

select ...
from ... join payments on ...
where ...
group by ...
having total != sum(payments.amount)

If most rows are imbalanced (unlikely), an alternative can be a correlated subquery:

select ...
from ...
where ...
and total != (select sum(amount) from payment where ...)

(Don't do the above if most rows are imbalanced, because performance will suck.)

A last alternative (which I'd actually recommend) is to maintain a paid column -- ideally, though not necessarily, using triggers. You could then use:

select ...
from ...
where ...
and total != paid

Better yet, you can rephrase the latter like:

where total - paid != 0

And then add a partial index on (total - paid) where (total - paid != 0), for maximum performance.

Upvotes: 3

Related Questions