Selecting popular models based on count of has_many relationship in Rails

Question

I have two models:

Novel has_many :pages
Page belongs_to :novel

I want to list popular Novels according to page count. Essentially, I want Novel models loaded from the outcome of this query:

select p.novel_id, count(*) as count
from pages p
GROUP BY p.novel_id
ORDER BY count DESC

I'm sure there's some cute way to do it in Rails 2.3 using named_scope, but I can't quite get it to work. Plus, if it does work, is it going to be dog slow?

I've considered keeping page_count on Novel, but that seems like a violation of something (convention, normalization, my soul).

Answer 1

Yep, that's going to be pretty slow. It's not a horrible thing to cache the page_count in your Novel. Normalization is all well and good, until it impacts performance.

Caching expensive calculations is the essence of most optimizations.

Answer 2

Seems like counter cache is the way to go. If you create a column called page_count on the novels table (with an index), Rails will cache the number of pages on the Novel model itself, making this kind of query very easy and performant.

The named_scope on the Novel model then becomes

class Novel < ActiveRecord::Base
  named_scope :popular, :order => 'page_count desc'
end

class Page < ActiveRecord::Base
  belongs_to :novel, :counter_cache => true
end

For more details check out the counter cache railscast

Answer 3

Keeping a counter_cache on the Novel is deemed acceptable in this matter and should aid your query.

In page.rb do:

 belongs_to :novel, :counter_cache => true

And in your novels table put a pages_count column. This will be automatically incremented when you create pages and decremented when you remove them.