Rails efficient SQL for "Popular" users

Question

I'm building a blog-type website in Rails (3) where "Authors" can follow other authors and in order and in order to find authors with the most followers I wrote this scope:

scope :popular, all.sort { |a, b| a.followers.count <=> b.followers.count }

I know this is really inefficient as it has to perform multiple queries for each author in the database and then compares the result. I'm asking this question to see if there isn't a better way of doing this. The relevant code is as follows:

class Author < ActiveRecord::Base

  has_many :relationships, :dependent => :destroy, :foreign_key => "follower_id"
  has_many :following, :through => :relationships, :source => :followed
  has_many :reverse_relationships, :dependent  => :destroy, :foreign_key => "followed_id",  :class_name => "Relationship"
  has_many :followers, :through => :reverse_relationships,  :source => :follower

  scope :popular, all.sort { |a, b| a.followers.count <=> b.followers.count }

end

The "followers" are implemented through a separate model:

class Relationship < ActiveRecord::Base

  belongs_to :follower, :class_name => "Author"
  belongs_to :followed, :class_name => "Author"

end

And for retrieving popular authors:

@popular_authors = Author.popular[0..9]

I'd love to be able to write something like Author.popular.limit(10) instead, but I understand that in order for this to work, Author.popular has to return an object of the ActiveRecord::Relation class, rather than an array.

As I mentioned, this code does work, but it's horribly inefficient, issuing hundreds of queries just to find the top 10 authors. Any help would be greatly appreciated!

Answer 1

One optimization might be to use eager loading. I suspect you have many queries that say, SELECT * fromAuthorWHEREfollower_id= 7

Eager loading can turn your hundreds of queries into a giant one for you. This might generate a slow query, but often times will faster because the time for the slow query was less than the 5000 fast queries.

I am not a SQL guru, but you might also want to use a GROUP_BY with LIMIT = 10. to get the 10 most popular.

Try something like

Authors.find(:all, :include => Authors, :group_by => " SELECT `count` as (some subquery I don't know to get the number of followers)", :limit => 10)

Scroll down to Eager loading of associations