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.
我有一个关于Rails 3 ActiveRecord :: Base.connection.execute方法和参数的语法问题,我想用它。我一直在与这个斗争几个小时,似乎没有在互联网上找到这个具体问题的任何答案。
The database is MySQL.
数据库是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:
我需要通过SELECT和IN条件语句创建一个临时表,其中IN应该检查的值列表是一个参数 - rails Array。代码如下:
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!
我收到MySQL语法错误!
ActiveRecord::Base.connection.execute("CREATE TEMPORARY TABLE things SELECT * FROM objects WHERE objects.id IN #{(arr)}")
Again MySQL syntax error!
再次MySQL语法错误!
ActiveRecord::Base.connection.execute("CREATE TEMPORARY TABLE things SELECT * FROM objects WHERE objects.id IN (#{arr})")
MySQL syntax error
MySQL语法错误
The above attempts correspond to this question-answer: How to execute arbitrary parameterized SQL in rails
上面的尝试对应于这个问题:如何在rails中执行任意参数化SQL
I even tried to use in the manner like with find_by_sql, but still get an error:
我甚至尝试以与find_by_sql类似的方式使用,但仍然出现错误:
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!
我错过了一些明显的东西吗请帮忙!我确切地以这种方式需要它(例如,创建具有这种条件的临时表),否则基于此的更复杂的查询将不起作用。谢谢!
2 个解决方案
#1
1
If you convert arr.to_s you get "[1, 2, 3]".
如果你转换arr.to_s你会得到“[1,2,3]”。
I think arr.join(', ') should work.
我认为arr.join(',')应该有效。
ActiveRecord::Base.connection.execute("CREATE TEMPORARY TABLE things SELECT * FROM objects WHERE objects.id IN (#{arr.join(', ')})")
#2
2
Here's another option that uses Rails' query sanitization
这是使用Rails查询清理的另一个选项
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)
#1
1
If you convert arr.to_s you get "[1, 2, 3]".
如果你转换arr.to_s你会得到“[1,2,3]”。
I think arr.join(', ') should work.
我认为arr.join(',')应该有效。
ActiveRecord::Base.connection.execute("CREATE TEMPORARY TABLE things SELECT * FROM objects WHERE objects.id IN (#{arr.join(', ')})")
#2
2
Here's another option that uses Rails' query sanitization
这是使用Rails查询清理的另一个选项
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)