2

I have a syntax question regarding the Rails 3 ActiveRecord::Base.connection.execute method and parameter that i want to use with it. I have been fighting with this for some hours now and just do not seem to find any answers to this specific question on the internet.

The database is MySQL.

I need to create a temporary table through SELECT with IN condition statement, where the list of values against which IN should check, is a parameter - rails Array. The code looks like so:

arr = [1,2,3]
ActiveRecord::Base.connection.execute("CREATE TEMPORARY TABLE things SELECT * FROM objects WHERE objects.id IN #{arr}")

I get a MySQL syntax error!

ActiveRecord::Base.connection.execute("CREATE TEMPORARY TABLE things SELECT * FROM objects WHERE objects.id IN #{(arr)}")

Again MySQL syntax error!

ActiveRecord::Base.connection.execute("CREATE TEMPORARY TABLE things SELECT * FROM objects WHERE objects.id IN (#{arr})")

MySQL syntax error

The above attempts correspond to this question-answer: How to execute arbitrary parameterized SQL in rails

I even tried to use in the manner like with find_by_sql, but still get an error:

ActiveRecord::Base.connection.execute(["CREATE TEMPORARY TABLE things SELECT * FROM objects WHERE objects.id IN (:ids)",{:ids => arr }]) - obviously, I get an MySQL error.

Am I missing something obvious? Please help! I need this exactly in this way (e.g. create temporary table with exactly such conditions), otherwise a more complicated query based on this one will not work. Thanks!

Community
  • 1
  • 1
zima
  • 673
  • 1
  • 9
  • 16

2 Answers2

5

Here's another option that uses Rails' query sanitization

arr = [1,2,3]
query = "SELECT * FROM objects where id IN (?)"
query = ActiveRecord::Base.send :sanitize_sql_array, [query, arr]
ActiveRecord::Base.connection.execute(query)
johnml
  • 453
  • 1
  • 6
  • 16
1

If you convert arr.to_s you get "[1, 2, 3]".

I think arr.join(', ') should work.

ActiveRecord::Base.connection.execute("CREATE TEMPORARY TABLE things SELECT * FROM objects WHERE objects.id IN (#{arr.join(', ')})")
mm1
  • 364
  • 2
  • 14