CODEWITHSUNDEEP
ruby-on-railsactiverecord
user684934
user684934

Reputation:

ActiveRecord Select with Parameter Binding

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?

Upvotes: 6

Views: 5522

Answers (1)

georgebrock
georgebrock

Reputation: 30043

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()

Upvotes: 9

Related Questions