I have an
Order model, and it has attributes
payment_due_date. In the UI, I need to show the count and total sum of orders which are between 1 and 30 days past due, and also between 30 and 60 says past due.
In order to do this, we use a FILTER clause in Postgres. We have to manually construct the SELECT statement and do a bunch of Arel manipulation.
Postgres and SqlLite have the ability to use the recently added Arel Filter which can apply to aggregate queries. So, we can extend all of the calculation methods (
.sum, etc) to accept the same
.where does (the underlying SQL syntax is FILTER ( WHERE … )).
“Wait, couldn’t you just use a normal
.where in the query?”
Yes - this is not particularly useful without considering my other proposal, which allows
.calculate to run multiple calculations at once. I figured it would make sense to split the proposals because they are two separate features.
# Sum of orders.total where payment_due_date is between 1 and 30 days ago account.orders.sum(:total, payment_due_date: 30.days.ago..1.day.ago) # Count of orders where the nickname is "Joe's Order" account.orders.count(["nickname = ?", "Joe's Order"])
With the other proposal regarding multiple calculations, this makes more sense:
account.orders.calculate( [:sum, :total, payment_due_date: 30.days.ago..1.day.ago], [:sum, :total, payment_due_date: 60.days.ago..30.days.ago], [:count, payment_due_date: 30.days.ago..1.day.ago] [:count, payment_due_date: 60.days.ago..30.days.ago] )
(or whatever interface for multiple calculations comes out of the other proposal)
For DBs that do not support FILTER, a CASE statement would work: The FILTER clause: Selective Aggregates