CODEWITHSUNDEEP
ruby-on-railsruby
user1675011
user1675011

Reputation:

Making this ActiveRecord query more efficient?

I have User and Gift models. A user can send gifts to another users. I have a relational table telling me which users received a gift. On the other hand, a user belongs to a School, which can be free or paid.

I want the count of users that have received a gift in the last week for a specific type of school (this is, free or paid).

I can do:

Gift.joins(:schools).where("created_at >= ? AND schools.free_school = ?", Time.now.beggining_of_week, true).collect(&:gift_recipients).flatten.uniq.count.

Or, I want to know how many users sent gifts the last week. This works:

Gift.joins(:schools).where("created_at >= ? AND schools.free_school = ?", Time.now.beggining_of_week, true).collect(&:user_id).uniq.count.

If I want to know how many users have sent or received a gift in the last week I can do:

(Gift.joins(:schools).where("created_at >= ? AND schools.free_school = ?", Time.now.beggining_of_week, true).collect(&:gift_recipients).flatten + Gift.joins(:schools).where("created_at >= ? AND schools.free_school = ?", Time.now.beggining_of_week, true).collect(&:user_id)).uniq.count

All this works fine but if the database is big enough this is really slow. Do you have any suggestions to make it more efficient, maybe using raw SQL where needed?

"gifts" 
  user_id (integer) 
  school_id (integer) 
  created_at (datetime) 
  updated_at (datetime) 
"gift_recipients" is a table like 
  gift_id | recipient_id,

Upvotes: 1

Views: 447

Answers (2)

user1675011
user1675011

Reputation:

I saw this old post and I wanted to make a couple of comments: As Winfield said 

Gift.joins(:school).where("created_at >= ? AND schools.free_school = ?", Time.now.beggining_of_week, true).count('distinct user_id')

is a good way of doing this. I would do

Gift.joins(:school).count('distinct user_id', :conditions => ["gifts.created_at >= ? AND free_school = ?", Time.now.beginning_of_week, true])

but just because this is nicer to my eyes, a personal thing, you can check that both produces exactly the same SQL query. Note that is necessary to write 

gifts.created_at

to avoid ambiguity because both tables has a column with this name, in the case of the column name

free_school

there is no ambiguity as this is not a column name in gifts tables. For the first query i was doing

Gift.joins(:school).where("created_at >= ? AND schools.free_school = ?", Time.now.beginning_of_week, true).collect(&:user_id).uniq.count

which is awkward. This works better

Gift.joins(:school).count("distinct user_id", :conditions => ["gifts.created_at >= ? AND free_school = ?", Time.now.beginning_of_week, true])

which avoid the problem of bringing gifts to memory and filtering them with ruby.

Up to this there's nothing new. The key point here is that my problem was calculating the number of users who sent or received a gift during the last week. For this I came up with the following

  senders_ids = Gift.joins(:school).find(:all, :select => 'distinct user_id', :conditions => ['gifts.created_at >= ? AND free_school = ?', Time.now.beginning_of_week, type]).map {|g| g.user_id}
  receivers_ids = Gift.joins(:school).find(:all, :select => 'distinct rec.recipient_id', :conditions => ['gifts.created_at >= ? AND free_school = ?', Time.now.beginning_of_week, type], :joins => "INNER JOIN gifts_recipients as rec on rec.gift_id = gifts.id").map {|g| g.recipient_id}
  (senders_ids + receivers_ids).uniq.count

I'm pretty sure that exists a better way of doing this, I mean, returning exactly this number in a single SQL query, but at least the results are arrays of objects containing only the id (recipient_id for the receivers case), not bringing all objects into memory. Well this is just hoping to be useful for someone new to sql queries through rails like me :).

Upvotes: 0

Winfield
Winfield

Reputation: 19145

You do not want to do this using collect(), which is loading all of the results into memory and filtering them within an Array of ActiveRecords. This is slow and dangerous, as it could potential leak/use all of the memory available, depending on the size of the data vs. your server.

Once you post your schema I can help you query/aggregate this in SQL, which is the right way to do it.

For example, instead of:

Gift.joins(:schools).where("created_at >= ? AND schools.free_school = ?", Time.now.beggining_of_week, true).collect(&:user_id).uniq.count

You should use:

Gift.joins(:schools).where("created_at >= ? AND schools.free_school = ?", Time.now.beggining_of_week, true).count('distinct user_id')

...which will count the distinct user_ids in SQL and return the result instead of returning all of the objects and counting them in memory.

Upvotes: 1

Related Questions