How do I optimize this sort by has_many relation count

This works, but something tells me I really should do it in SQL.

Which I'm really bad at, so please help me.

I want to sort my users by their amount of gestures in the current or a specified month.

I have this in User.rb:

def self.mayor(month = nil, year = nil)
  month = Date.today.month if !month
  year = Date.today.year if !year
  self.all.sort { |a, b|
    b.gestures.done_in_month(month, year).count <=>
    a.gestures.done_in_month(month, year).count }.first
end

and this in gesture.rb:

scope :done_in_month, lambda { |*args|
  return nil if !args[0]
  date = Date.new((args[1] || Date.today.year), args[0])
  where(:done_on => date.begin开发者_运维知识库ning_of_month..date.end_of_month)
}

Any help or guiding is greatly appreciated!

Use this

def self.mayors(month = Date.today.month, year = Date.today.year)
  date = Date.new(year, month)
  self.group("users.id").
    joins(:gestures).
    select("users.*, COUNT(users.id) as gestures_count").
    order("gestures_count DESC").
    where(:gestures => {:done_on => date.beginning_of_month..date.end_of_month})
end

def self.mayor(month = Date.today.month, year = Date.today.year)
  self.mayors(month, year).first
end

Like:

User.mayors(1,2011).map(&:gestures_count)
#=> [20, 15, 15, 12, 3]

mayor = User.mayor
puts "Our mayor user is: " + mayor.name + ", with " + mayour.gestures_count + " gestures"