CODEWITHSUNDEEP
ruby-on-railsrubyrails-activerecord
Wilduck
Wilduck

Reputation: 14096

Pre-cache an initial query, to have sub-queries not hit the database

I have two tables, Users and Responses, where there are many responses per user. I'm looking to do some computation on the set of responses for each user. Something like the code below:

all_users = User.all
all_rs = Responses.all

all_users.map { |u| all_rs.where(user: u).count }

As written this will make a call to the database for each user. Is there a way to pre-cache the all_rs data, so that each subsequent where is done in memory?

The logic above could probably be easily written in sql, but imagine that the code in the map block contained a lot more work.

Upvotes: 0

Views: 182

Answers (3)

Frederick Cheung
Frederick Cheung

Reputation: 84114

You could do something like

responses_by_user = Response.all.group_by(&:user_ud)

(assuming that Response has a user_id attribute)

You could then do responses_by_user[user.id] to get the responses for a user without any further queries. Do be careful of the overhead of creating all these extra ActiveRecord objects. As you hint, the very specific example you give can be handled by an sql group/count, which would probably be a lot faster.

Upvotes: 1

Aetherus
Aetherus

Reputation: 8888

What you need is counter cache (see section 4.1.2.3 of Rails Guide).

To enable counter cache, first, add or change your migration file:

db/migrate/add_counter_cache_to_users.rb

class AddCounterCacheToUsers < ActiveRecord::Migration
  def change
    # Counter cache field
    add_column :users, :responses_count, :integer, null: false, default: 0
    # Optionally add index to the column if you want to `order by` it.
    add_index :users, :responses_cache
  end
end

Then modify your model classes

app/models/response.rb

class Response < ActiveRecord::Base
  belongs_to :user, counter_cache: true
end

Run rake db:migrate. From now on, whenever a Response is created, the value of users.responses_count column will automatically increment by 1, and whenever a Response is destroyed, that column's value will decrement by 1.

If you want the count of someone's responses, just call responses_count on that user.

user = User.first
user.responses_count  #=> the responses count of the user
# or
user.responses.size

Your original requirement can be fulfilled with

User.select(:responses_count).to_a

UPDATE

I can't think how can I have missed such a bloody easy solution

Response.group(:user_id).count

Upvotes: 1

Ravi Sankar Raju
Ravi Sankar Raju

Reputation: 2950

something like this maybe ? assuming users has_many responses

all_users = User.includes(:responses)
user_responses_count = all_users.map { |u| u.responses.count }

this will not query for responses 1000 times, in cases you have 1000 users. Let me know if this works for your use-case.

Note that this query level caching. No model changes are required..

Upvotes: 0

Related Questions