CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
Marc
Marc

Reputation: 1043

inserting variable into complex sql command

I have a set-up with multiple contests and objects. They are tied together with a has_many :through arrangement with contest_objs. contest_objs also has votes so I can have several contests including several objects. I have a complex SQL setup to calculate the current ranking. However, I need to specify the contest in the SQL select statement for the ranking. I am having difficulty doing this. This is what I got so far:

@objects = @contest.objects.select('"contest_objs"."votes" AS v, name, "objects"."id" AS id, 
                                   (SELECT COUNT(DISTINCT "oi"."object_id")
                                           FROM  contest_objs oi
                                           WHERE ("oi"."votes") > ("contest_objs"."votes"))+1 AS vrank')

Is there any way in the selection of vrank to specify that WHERE also includes "oi"."contest_id" = @contest.id ?

Upvotes: 1

Views: 4565

Answers (3)

mclin
mclin

Reputation: 3629

Use sanitize_sql_array:

sanitize_sql_array('select ? from foo', 'bar')

If you're outside a model, because the method is protected you have to do this:

ActiveRecord::Base.send(:sanitize_sql_array, ['select ? from foo', 'bar'])

http://apidock.com/rails/ActiveRecord/Sanitization/ClassMethods/sanitize_sql_array

Upvotes: 2

Felix
Felix

Reputation: 812

You can insert variables into sql commands like this:

Model.select("...... WHERE id = ?", @contest.id)

Rails will escape the values for you.

Edit:

This does not work as stated by Intrepidd in the comments, use string interpolation like he suggested in his answer. That is safe for integer parameters.

If you find yourself inserting several strings in a query, you could consider using find_by_sql, which gives you the above mentioned ? replacement, but you can't use it with chaining, so rewriting the whole query would be needed.

Upvotes: -1

Intrepidd
Intrepidd

Reputation: 20878

Since @contest.id is an integer and does not present any risk of an SQL Injection, you could do the following using string interpolation :

Model.select("..... WHERE id = #{@contest.id}")

Another possible solution would be to build your subquery using ActiveRecord, and then call .to_sql in order to get the generated SQL, and insert it in your main query.

Upvotes: 3

Related Questions