CODEWITHSUNDEEP
ruby-on-railsdatabaseperformanceactiverecord
volx757
volx757

Reputation: 163

rails database query efficiency with multiple associations

I have a database with about 1000 clubs and 10,000 users. I need to build a table displaying each club in a row, with columns for number of users, posts, and comments associated with that club. The query I have now goes like this (from my controller):

@clubs.find_each do |club|
    @users.where(:club_id => club.club_id).find_each do |user|
         @numPostsByClub += user.posts.count
         @numCommentsByClub += user.comments.count

Now I imagine there must be a more efficient way of doing this, as it takes about 90 seconds for my page to load. Is there either a more efficient way to load all of this data, or perhaps a way to run a rake task (I only need updated data once a day) that stores all of this for quick access?

Upvotes: 1

Views: 243

Answers (2)

cristian
cristian

Reputation: 8744

If I understand you correctly, you want the total number of comments by club and the total number of comments by club.

counter_cache will help you if you want to have quick answers for questions like:

  1. how many users a club has, or
  2. how many comments a user has, or
  3. how many posts a user has

but not with total number of posts or comments for each club (although it will make your live a little easier).

Disclaimer: Do not start implementing the following advices into production without creating a backup of your database and without reading the whole answer.

To make your script faster start by adding 2 columns to your clubs table:

class AddCommentsCountToClubs < ActiveRecord::Migration
  def change
    add_column :clubs, :comments_count, :integer, default: 0
  end
end

class AddPostsCountToClubs < ActiveRecord::Migration
  def change
    add_column :clubs, :posts_count, :integer, default: 0
  end
end

For each club:

  1. update comments_count to contain the number of comments added by users that belongs to the club
  2. update posts_count to contain the number of posts added by users that belongs to the club

To create a rake task that will update the counters, add a file lib/tasks/update_clubs_counters.rake with the following content:

namespace :db do
  task :update_clubs_counters => :environment do
    Club.all.each do |club|
      club.update(comments_count: club.comments.count, posts_count: club.posts.count)
    end
  end
end

After you create the file run bundle exec rake db:update_clubs_counters

Another way to update your counters is to use the rails console and to run the content of the task (only the part relevant to update)

Then, for Comment and Post models, add 2 callbacks to increment/decrement the counters from each corresponding club.

For clarity, I will define all the models involved and the relations between them

class Club < ActiveRecord::Base
  has_many :users
  has_many :comments, through: :users
  has_many :posts, through: :users
end

class User < ActiveRecord::Base
  belongs_to :club 
  has_many :posts
end

class Comment < ActiveRecord::Base
  belongs_to :user

  after_create :increment_club_comments_count
  after_destroy :decrement_club_comments_count     

  def increment_club_comments_count
    Club.increment_counter( :comments_count, user.club_id )
  end

  def decrement_club_comments_count
    Club.decrement_counter( :comments_count, user.club_id )
  end
end

class Post < ActiveRecord::Base
  belongs_to :user

  after_create :increment_club_posts_count
  after_destroy :decrement_club_posts_count

  def increment_club_posts_count
    Club.increment_counter( :posts_count, user.club_id )
  end

  def decrement_club_posts_count
    Club.decrement_counter( :posts_count, user.club_id )
  end
end

Now, every time a post/comment is added/deleted the corresponding counter from clubs table is incremented/decremented.

You can simplify your controller like this (with only one query you will have all your data):

@clubs = Club.all # I recommend to use pagination and not to list all 1000 clubs at once

In your view you simply display your counters like this:

<% @clubs.each do |club| %>
  <p>Comments Count: <%= club.comments_count %></p>
  <p>Posts Count: <%= club.posts_count %></p>     
<% end %>

You can find more details about increment_counter and decrement_counter and counter_cache with has_many :through

Upvotes: 1

NolanDC
NolanDC

Reputation: 1041

Check out the counter_cache option for your belongs_to associations. This adds a field to the parent model to cache the number of child objects. When a child object is created or deleted, this field is updated to maintain the correct count.

More information can be found in the Association Basics section of the Ruby on Rails Guide.

RailsCasts also has an episode about the counter_cache option, with an example implementation: Example #23

Upvotes: 0

Related Questions