I''ve been grimacing at my test app for a while, and now it''s
really bugging me.
background
=========
It''s a simple game called CNPS - ''consecutive number plate
spotting'' (no, it''s *not*
a bit like trainspotting. how dare you.)
So we have a player who at any given time has a score between 0 and 999.
Rather than just have a ''highscore'' attribute, I wanted to
play around with a Score
object which holds the number spotted, the date, comments etc.
So basically we hane a dead simple
class Player < ActiveRecord::Base
has_many :scores
end
class Score < ActiveRecord::Base
belongs_to :player
end
there''s a load of validation, etc in there too - a Player is
essentially a User for
authentication, yada yada, all is well in the world.
problem
======
*but* in the app I generally want a ''leader board'' view, so I
want to sort players
by their high scores, figure out who''s winning and so on.
Essentially I want to treat the players highest Scores number as an attribute of
Player.
I have something that works if I add a couple of methods to Player:
class Player
# highest of the Scores
def highscore
Score.find( :first,
:conditions => [''player_id = ?'', id ],
:order => ''number DESC''
)
end
# pseudo-attribute to allow easy sorting of players by their scores
def high_number
h = self.highscore
h.nil? ? 0 : h.number
end
end
but obviously this kicks the living sh*t out of the db - the same query is made
several times when I do stuff like:
rasputnik@eris:rails$ less app/views/cnps/scoreboard.rhtml
<h2>top <%= @count %> players</h2>
<div id="scoreboard">
<% sorted = @players.sort { |a,b|
b.high_number <=> a.high_number
} %>
<ul>
<% sorted[0,@count].each { |p| %>
<li>
<%= "#{p.username} has a score of #{p.high_number}"
%>
</li>
<% } %>
</ul>
</div>
argh
===
Admittedly this code is just lazily thrown together, but I don''t really
want to
hane to avoid easily readable code for purely performance reasons. Sorting on an
attribute
when you need to ''feels'' better than having a controller
return an ordered list, for example...
it really feels like there should be a fix I could just apply to the model to
avoid the rest
of the app having to worry about this.
Sadly, caching seems to be a view level thing, so that doesn''t help,
and eager loading
doesn''t seem to apply in this case.
Is there a way to ''cache'' arbitrary SQL?
--
''Some people, when confronted with a problem, think I know,
I''ll use
regular expressions. Now they have two problems.''
-- Jamie Zawinski
Rasputin :: Jack of All Trades - Master of Nuns