Hello Railists,
I have a piece of Ruby on Rails code that has a complex SQL query (well,
not
that complex, but as far as I know beyond the ORM capabilities) and for
my
taste it has too many strings and harcoded values. I’d like to improve
it as
much as possible, so my question is open ended, what else can I do to
improve it?
Some particular issues I have
- Is there a way to get a table name to use it in a query in the same
escaped way as the ORM does? I think this is database independent,
beingitemsfor MySQL but not for other databases. - In the same vein, is there a way to get a field name the same way
Rail’s
ORM would put it in a SQL query? - Maybe there’s a way to get both, the table name and the field name in
one
operation. I’m imaging something like Item.voteable_id.for_query
=> “items.voteable”. - How do I escape code to avoid SQL injection when not in conditions?
I’m
using the user_id variable directly in a query and although it’s
impossible
for a user to put anything in it, I’d rather escape it properly. In a
condition I would do [‘user_id = ?’, user_id], but in a join or a
select,
how do I do it? - Does my use of class constants here make sense?
- Is there any chance at all of using the ORM and less string?
- Any other thing to do to it?
The code is this one
class Item < ActiveRecord::Base
has_many :votes, :as => :voteable
def self.ranking(user_id)
Item.find(:all,
# items.* for all the Item attributes, score being the sum of
votes,
user_vote is the vote of user_id (0 if no vote) and voter_id is just
user_id
for latter reference.
:select => “items.*,
IFNULL(sum(all_votes.value), 0) as score,
user_votes.value as user_vote,
"#{user_id}" as voter_id”,
# The first join gets all the votes for a single item (to be
summed
latter).
# The second join gets the vote for a single user for a single
item.
:joins => [“LEFT JOIN votes as all_votes ON
all_votes.voteable_id = items.id and
all_votes.voteable_type = "Item"”,
“LEFT JOIN votes as user_votes ON
user_votes.voteable_id = items.id and
user_votes.user_id = "#{user_id}" and
user_votes.voteable_type = "Item"”
],
:group => :id,
:order => “score DESC”)
# This is the query it should generate
# SELECT items.*, user_votes.value as user_vote,
sum(all_votes.value) as
score
# FROM items
# LEFT JOIN votes as all_votes ON
# all_votes.voteable_id = items.id and
# all_votes.voteable_type = “Item”
# LEFT JOIN votes as user_votes ON
# user_votes.voteable_id = items.id and
# user_votes.user_id = 2 and
# user_votes.voteable_type = “Item”
# GROUP BY items.id
# ORDER BY score DESC
end
def score
s = read_attribute(“score”)
if s == nil
votes.sum :value
else
Integer(s)
end
end
def user_vote(user_id)
if Integer(read_attribute(“voter_id”)) == user_id
Integer(read_attribute(“user_vote”))
else
vote = votes.find(:first, :conditions => [“user_id = ?”, user_id])
if vote
vote.value
else
0
end
end
end
end
Thanks.
J. Pablo Fernández [email protected] (http://pupeno.com)