Avoiding individual database calls for count

Question

My models look like this:

class Movie < ActiveRecord::Base
  attr_accessible :title, :year, :rotten_id, :audience_score,
    :critics_score, :runtime, :synopsis, :link, :image

  has_many :jobs, :dependent => :destroy
  has_many :actors, :through => :jobs
end

class Actor < ActiveRecord::Base
  attr_accessible :name
  has_many :movies, :through => :jobs
  has_many :jobs, :dependent => :destroy
end

class Job < ActiveRecord::Base
  attr_accessible :movie_id, :actor_id

  belongs_to :movie
  belongs_to :actor
end

When I'm displaying my index of Actors, I'd like to show the number of movies each actor has starred in. I can do this with @actor.movies.count, however this generates an SQL query for each actor. With, say, 30 actors, this will result in 30 extra queries in addition to the initial.

Is there any way to include the count of movies each actor has participated in, in the initial Actor.all call? And thereby getting things done with only one call. Extra bonus if this was sorted by said count.

Update: All answers provided has been helpful, and though it turned into some dirt-slinging-contest at some point, it worked out well. I did a mish-mash of all your suggestions. I added a movies_counter column to my Actor model. In my Job model I added belongs_to :actor, :counter_cache => :movies_counter. This works brilliantly, and is automatically updated when i create or destroy a movie, without me adding any further code.

fl00r · Accepted Answer · 2011-03-19T22:07:34.313

2

As @Sam noticed, you should add new column to actors table movies_counter

rails g migration add_movies_counter_to_actor movies_counter:integer

Now you can edit your migration

class AddMoviesCounterToActor < ActiveRecord::Migration
  def self.up
    add_column :actors, :movies_counter, :integer, :default => 0

    Actor.reset_column_information
    Actor.all.each do |a|
      a.update_attribute :movies_counter, a.movies.count
    end
  end

  def self.down
    remove_column :actors, :movies_counter
  end
end

And run it

rake db:migrate

Then you should add two callbacks: after_save and after_destroy

class Movie < ActiveRecord::Base
  attr_accessible :title, :year, :rotten_id, :audience_score,
    :critics_score, :runtime, :synopsis, :link, :image

  has_many :jobs, :dependent => :destroy
  has_many :actors, :through => :jobs

  after_save :update_movie_counter
  after_destroy :update_movie_counter

  private
  def update_movie_counter
    self.actors.each do |actor|
      actor.update_attribute(:movie_count, actor.movies.count)
    end
  end
end

Then you can call some_actor.movies_counter

edited Mar 19 '11 at 22:07

answered Mar 19 '11 at 21:54

fl00r

82,987
33
217
237

you're reimplementing the counter_cache feature. – Adam Lassek Mar 19 '11 at 22:12
Here is has_many :through association, which haven't got counter_cache option – fl00r Mar 19 '11 at 22:13
You're right, this looks like the best solution although you should probably be using `Actor.increment_counter` instead of doing a full count each time. – Adam Lassek Mar 19 '11 at 22:32
@Adam, it can be increment or decrement (when deletes). So we can write two methods for each callback with `increment_counter` and `decrement_counter` or we can use this solution, which has got one more sql request, but it is not importaint if project is not kind of highload – fl00r Mar 20 '11 at 10:34

thenengah · Answer 2 · 2011-03-19T22:10:42.257

0

Add a column to your Actor table called 'movie_count'. Then add a call back in your Actor model that updates that column.

class Movie < ActiveRecord::Base
  has_many :actors, :through => :jobs
  before_save :update_movie_count
  def update_movie_count
     self.actor.update_attribute(:movie_count, self.movies.size)
  end
end

That way your just have an integer that gets updated instead of calling all records.

edited Mar 19 '11 at 22:10

answered Mar 19 '11 at 21:55

thenengah

42,557
33
113
157

I would do the update_movie_count in my Job model though, right? As a movie has several actors, and it's not really until a Job is created that the count should increase. But yeah, I get the idea. It's a simple work around when you're aware of utilities as before_save. Thanks – leflings Mar 19 '11 at 22:11
@sam you're reimplementing the counter_cache feature. – Adam Lassek Mar 19 '11 at 22:12
@sam actually it doesn't quite work here, so you'd have to reimplement it. – Adam Lassek Mar 19 '11 at 22:26
@Adam Lassek. and besides as @flOOr pointed out your answer that you deleted was wrong because this is a has_many through so I'm not reimplementing the counter_cache. – thenengah Mar 19 '11 at 22:26
:counter_cache doesnt work, as stated... but will some implementation of :counter_sql work? – leflings Mar 19 '11 at 22:29

Avoiding individual database calls for count

2 Answers2