处理HABTM关系,需要编写一个sql查询字符串,我可以通过ActiveRecord::Base.connection.execute(my_query)
运行
SQL,其中。。。IN子句需要一个整数id数组,如下所示:"DELETE from contacts_tags where tag_id = 99 AND contact_id IN (1, 2, 3, 4)
我有一个id数组,我想将其传递到IN子句中,但似乎无法以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.
向SQL WHERE子句传递id数组的正确方法是什么?
尝试:
my_query = "DELETE from contacts_tags where tag_id = 99 AND contact_id IN (#{ids_for_delete.join(', ')})"
使用join方法生成类似"1,2,3,4.."的字符串
您还可以对数组值使用ANY操作:
DELETE FROM contacts_tags WHERE tag_id = 99 AND contact_id = ANY('{ #{ids_for_delete.join(",")} }'::int[])
即使您传递一个空数组,它也不会中断。