CODEWITHSUNDEEP
ruby-on-railsrubyperformanceactiverecord
nobody
nobody

Reputation: 8293

Active Record Query with range of users

So I want to optimize my ActiveRecord querying to do something more efficient. Currently when I query using the methods that follow I get something like the following:

SELECT "expirations".* FROM "expirations" WHERE (user_id = 14 and expire_on <= '2014-09-25')
SELECT "expirations".* FROM "expirations" WHERE (user_id = 15 and expire_on <= '2014-09-25')
SELECT "expirations".* FROM "expirations" WHERE (user_id = 17 and expire_on <= '2014-09-25')

etc. until the query completes for all users. What would be more efficient, and what I want to achieve is something like the following:

SELECT "expirations".* FROM "expirations" WHERE (user_id IN (14,15,17) and expire_on <= '2014-09-25')

Make sense? I'm not sure how to go about this though so some direction would be exceptional!

def expired_trainings(current_user)
    retval = []
    Organization.appropriate_users(current_user).each do |user|
      Expiration.where("user_id = #{user.id} and expire_on <= '#{Date.today}'").each do |expir|
        retval << expir
      end
    end
    ttypes = []
    if current_user.max_role.downcase == 'leader'
      current_user.leading_groups.each do |g|
        g.training_types.each do |tt|
          ttypes << tt
        end
      end
      ttypes.flatten.uniq!
      retval.delete_if { |expir| !ttypes.include?(expir.training_type) }
    end
    retval.flatten
  end

Upvotes: 0

Views: 49

Answers (1)

infused
infused

Reputation: 24337

You can simplify (and optimize) this block:

retval = []
Organization.appropriate_users(current_user).each do |user|
  Expiration.where("user_id = #{user.id} and expire_on <= '#{Date.today}'").each do |expir|
    retval << expir
  end
end

Down to this:

appropriate_users = Organization.appropriate_users(current_user)
retval = Expiration.where(user_id: appropriate_users).where(['expire_on <= ?', Date.today])

This will retrieve Expirations for all of the appropriate_users in a single query.

Upvotes: 1

Related Questions