mysql - Rails SQL 查询，将 id 数组传递给 sql WHERE ... IN 子句

Question

处理 HABTM 关系并需要编写一个我可以通过 ActiveRecord::Base.connection.execute(my_query) 运行的 sql 查询字符串

SQL WHERE...IN 子句需要一个整数 ID 数组，如下所示:"DELETE from contacts_tags where tag_id = 99 AND contact_id IN (1, 2, 3, 4)

我有一组要传递到 IN 子句中的 ID，但似乎无法按照 SQL 接受的方式对其进行格式化。

ids_for_delete = [1, 2, 3, 4]
my_query = "DELETE from contacts_tags where tag_id = 99 AND contact_id IN (#{ids_for_delete})"
ActiveRecord::Base.connection.execute(my_query) 
    #=> There is an error in your SQL syntax.

将 ID 数组传递给 SQL WHERE 子句的正确方法是什么？

Answer 1

尝试:

my_query = "DELETE from contacts_tags where tag_id = 99 AND contact_id IN (#{ids_for_delete.join(', ')})"

使用 join 方法生成字符串，如“1, 2, 3, 4..”