How to sort authors by their book count with ActiveRecord?

Question

Let's say I have Book model and an Author model. I want to list all authors sorted by their book count. What's the best way to do that?

I know how to do this in SQL, either by doing where .. in with a nested select or with some join. But what I'd like to know is how to do this nicely with ActiveRecord.

Answer 1

As Kevin has suggested, counter_cache is the easiest option, definitely what I would use.

class Author < ActiveRecord::Base
  has_many :books, :counter_cache => true
end

class Book < ActiveRecord::Base
  belongs_to :author
end

And if you are using Rails 2.3 and you would like this to be the default ordering you could use the new default_scope method:

class Author < ActiveRecord::Base
  has_many :books, :counter_cache => true

  default_scope :order => "books_count DESC"
end

books_count is the field that performs the counter caching behaviour, and there is probably a better way than using it directly in the default scope, but it gives you the idea and will get the job done.

EDIT:

In response to the comment asking if counter_cache will work if a non rails app alters the data, well it can, but not in the default way as Rails increments and decrements the counter at save time. What you could do is write your own implementation in an after_save callback.

class Author < ActiveRecord::Base
  has_many :books

  after_save :update_counter_cache

  private
    def update_counter_cache
      update_attribute(:books_count, self.books.length) unless self.books.length == self.books_count
    end
end

Now you don't have a counter_cache installed, but if you name the field in the database books_count as per the counter_cache convention then when you look up:

@Author = Author.find(1)
puts @author.books.size

It will still use the counter cached number instead of performing a database lookup. Of course this will only work when the rails app updates the table, so if another app does something then your numbers may be out of sync until the rails application comes back an has to save. The only way around this that I can think of is a cron job to sync numbers if your rails app doesn't do lookup up things often enough to make it not matter.

Answer 2

Taken from http://www.ruby-forum.com/topic/164155

Book.find(:all, :select => "author_id, count(id) as book_count", :group => "author_id", :order => "book_count")

Answer 3

I suggest counter caching the book count as another attribute on Author (Rails supports this with an option on the association). This is by far the fastest method, and Rails is pretty good about making sure it keeps the count in sync.