6

I want to use a subquery with ActiveRecord like so:

User.select(
    'users.*, 
    (select sum(amount) 
        from subscriptions 
        where subscriptions.user_id = user.id
        and created_at < ? ) as amt)'
).order('amt')

However, on the second to last line, I have the problem that I can't figure out how to bind the Time class parameter, as ActiveRecord::Base's select method doesn't accept more than one parameter (the sql string). What do I do?

1 Answers1

9

You can use one of the ActiveRecord::Sanitization class methods.

These are mixed into ActiveRecord::Base and are almost all protected so they are most easily accessed by defining a class method on your model:

class User < ActiveRecord::Base
  def self.select_with_args(sql, args)
    query = sanitize_sql_array([sql, args].flatten)
    select(query)
  end
end

It's also worth looking for other ways to fetch your data and comparing their performance to the subquery method. For example, you could fetch the subscription counts with a separate query:

subs_by_user = Subscription.group(:user_id).where('created_at < ?', d).count()
georgebrock
  • 28,393
  • 13
  • 77
  • 72
  • 1
    Wouldn't this prevent the use of the prepared statements cache in the db adapter? Looks like http://apidock.com/rails/ActiveRecord/Sanitization/ClassMethods/sanitize_sql_array just interpolates the current value into the statement. – qix Mar 18 '16 at 17:21