How can I improve a Ruby on Rails code that hast a lot of SQL as strings?

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 items for 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.

check out the acts as voteable plugin on github, looks like you’ve already got your modeling done correctly to just drop it in.

-C

Hello Chris,

I’ve checked. There was one little thing the act as votable couldn’t do; I can’t remember exactly what, maybe the negative votes or something like that. So I did my own.

But what I’m really after is improving the so many strings.