Limit query by sum of attributes of objects in Rails and Postgresql

Question

I have a Lead model that has a pre-calculated float column called "sms_price". I want to allow users to send text messages to those leads, and to assign a budget to their campaigns (something similar to what you can find on fb ads).

I need a scope that can limit the number of leads by the total price of those leads. So for example if the user has defined a total budget of 200

• id: 1 | sms_price: 0.5 | total_price: 0.5
• id: 2 | sms_price: 1.2 | total_price: 1.7
• id: 3 | sms_price: 0.9 | total_price: 2.6
• ...
• id: 94 | sms_price: 0.8 | total_price: 199.4 <--- stop query here
• id: 95 | sms_price: 0.7 | total_price: 200.1

So I need two things in my scope:

1. Calculate the total price recursively
2. Get only the leads that have a total price lower than the desired budget

So far I have only managed to do the first task (Calculate the total price recursively) using this scope:

scope :limit_by_total_price, ->{select('"leads".*, sum(sms_price) OVER(ORDER BY id) AS total_price')}

This works and if I do Lead.limit_by_total_price.last.total_price I get 38039.7499999615

Now what I need is a way to retrieve only the leads that have a total price lower than the budget:

scope :limit_by_total_price, ->(budget){select('"leads".*, sum(sms_price) OVER(ORDER BY id) AS total_price').where('total_price < ?', budget)}

But it doesn't recognise the total_price attribute:

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "total_price" does not exist

Why does it recognise the total_price attribute in a single object and not in the scope ?

Answer 1

The problem is that the columns calculated in a SELECT clause are not available to the WHERE clause in the same statement. To do what you want, you need a subquery.

You can do this and yet stay in the Rails universe using ActiveRecord's from method. The technique is nicely illustrated in this Hashrocket blog post.

In your case it might look something like this (because of the complexity, I would use a class method rather than a scope):

def self.limit_by_total_price(budget)
  subquery = select('leads.*, sum(leads.sms_price) over(order by leads.id) as total_price')
  from(subquery, :leads).where('total_price < ?', budget)
end