5

How I can optimise my SQL queries, to ignore situations like this:

Meeting.find(5).users.size => SELECT COUNT(*) FROM ... WHERE ...

User.find(123).meetings.size => SELECT COUNT(*) FROm ... WHERE ...

I have no idea how to use counter_cache here.

Here is my model relation:

class Meeting < ActiveRecord::Base
  has_many :meeting_users
  has_many :users, :through => meeting_users
end

class User < ActiveRecord::Base
  has_many :meeting_users
  has_many :meetings, :through => meeting_users
end

class Meeting_user < ActiveRecord::Base
  belongs_to :meeting
  belongs_to :user
end

What are the most optimal solutions ?

And how implement counter_cache here ?

Community
  • 1
  • 1
astropanic
  • 10,800
  • 19
  • 72
  • 132
  • Would anything here prevent you from doing something like MeetingUser.where(:meeting_id => 5, :user_id => 123).size for both situations? At least in that case you'd be able to take advantage of SQL query caching. The default implementation of counter_cache won't really help you in this sort of situation. – jmcnevin Jan 15 '11 at 15:27
  • 1
    I think You are wrong. counter_cache will help here. For example when You render a list of users in the view, and You will display in each line how many meetings each user has ? With counter_cache this will be one SQL query, without it will be 1+n*Users.size – astropanic Jan 15 '11 at 15:32

2 Answers2

22

Starting from Rails3.0.5 and in newer versions, you are now able to set counter cache to the "linker" model, in your case it will be:

class MeetingUser < ActiveRecord::Base
  belongs_to :meeting, :counter_cache => :users_count
  belongs_to :user, :counter_cache => :meetings_count
end

It's important to explicitly specify count column names, otherwise the columns used will default to meeting_users_count.

Vitaliy Yanchuk
  • 1,463
  • 1
  • 11
  • 22
1

As far as I know you can't use counter_cache with through associations, that's why you should manually increment it.

For example (untested):

class MeetingUser < ActiveRecord::Base

  ...

  after_create { |record| 
    Meeting.increment_counter(:users_count, record.meeting.id)
  }

  after_destroy { |record| 
    Meeting.decrement_counter(:users_count, record.meeting.id)
  }

end
vise
  • 12,713
  • 11
  • 52
  • 64