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, being
itemsfor 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](http://items.id) and
all_votes.voteable_type = \"Item\"",
"LEFT JOIN votes as user_votes ON
user_votes.voteable_id = [items.id](http://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](http://items.id) and
# all_votes.voteable_type = "Item"
# LEFT JOIN votes as user_votes ON
# user_votes.voteable_id = [items.id](http://items.id) and
# user_votes.user_id = 2 and
# user_votes.voteable_type = "Item"
# GROUP BY [items.id](http://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.