Rails 3 connection.execute IN statement parameters array syntax

Question

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!

johnml · Accepted Answer · 2014-09-24 15:07:31Z

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)

answered Sep 24, 2014 at 15:07

johnml

4281 gold badge6 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mm1 · Accepted Answer · 2012-08-31 14:42:07Z

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(', ')})")

answered Aug 31, 2012 at 14:42

mm1

3642 silver badges14 bronze badges

Collectives™ on Stack Overflow

Rails 3 connection.execute IN statement parameters array syntax

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related