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 2009/11/8 J. Pablo Fernández <[email protected]> > 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 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) > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
