[Proposal] .sum, .count, and other .calculate methods should accept where filters

rubyonrails-core
,
1

Use Case

I have an Order model, and it has attributes total and 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.

Current Solution

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.

Proposal

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 (.count, .sum, etc) to accept the same *args that .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.

Examples:

# 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

4 Likes
2

A proper support for aggregate filter clause would be nice, but it is easy enough to do with existing functionality:

account.orders.pluck(
  "sum(total) filter (where (current_date - payment_due_date) between 1 and 30)",
  "sum(total) filter (where (current_date - payment_due_date) between 30 and 60)",
  "count(*) filter (where (current_date - payment_due_date) between 1 and 30)",
  "count(*) filter (where (current_date - payment_due_date) between 30 and 60)",
)

The only difficulty is safe argument interpolation. Would be nice if AR supported interpolation in all commands, not just where.

3

I believe aggregate filters are only useful when selecting multiple aggregates. For a single aggregate, a regular where clause is effectively the same:

account.orders.where(payment_due_date: 30.days.ago..1.day.ago).sum(:total)