Forum: Ruby on Rails Sort model based on max value of has_many field

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
John T. (Guest)
on 2008-11-10 20:41
Hi,

I have models such as:

class Team < ActiveRecord::Base
   has_many :games
end

class Game< ActiveRecord::Base
   belongs_to :team
end

So there are many (well, 0 to 6) games for each team. Team game has a
"score". I would like to get a list of the teams, sorted by the highest
scoring game - sort of a ranking based on highest score. I've tried
several cominations of find and sort, but can't figure it out. I did add
a helper to the Team model:

def high_score
  matches.maximum(:score)
end

but still am not sure how to get what I want. I basically want to
display a list of the teams, ranked by their highest score.

One stickler is, any given team may not have any games at a given time,
so team.matches may be null...

Thanks!

jt
Jeff E. (Guest)
on 2008-11-11 23:00
(Received via mailing list)
maybe something like so:


class Team < ActiveRecord::Base
  has_many :games

  def self.by_high_score
    self.all(
             :select => 'teams.id, teams.name, max(games.score)',
             :joins => 'left join games on team_id = teams.id',
             :group => 'teams.id',
             :order => 'score desc',
             :conditions => 'score is not null'
    )
  end

end



On Nov 10, 1:41 pm, "John T." <removed_email_address@domain.invalid>
Robert W. (Guest)
on 2008-11-12 16:39
>   def self.by_high_score
>     self.all(
>              :select => 'teams.id, teams.name, max(games.score)',
>              :joins => 'left join games on team_id = teams.id',
>              :group => 'teams.id',
>              :order => 'score desc',
>              :conditions => 'score is not null'
>     )
>   end
While this will probably work just fine, I would recommend caching the
high score in a attribute of the Team model. This would be very similar
to the counter_cache feature that Rails provides. This way your sorting
could be handled by a very simple named route. It would also be many
times more efficient since there is no join to deal with.

The trade-off is that you need additional logic to update the cached
value when necessary, and in making sure that you always rely on your
model's business logic to manage the cache. I personally think that the
performance gained though this technique is well worth the trade-off in
case where performance may be a significant factor.
This topic is locked and can not be replied to.