CODEWITHSUNDEEP
mysqlruby-on-rails
Jim
Jim

Reputation: 420

Rails SQL query, passing array of ids to sql WHERE...IN clause

Dealing with a HABTM relationship and need to write a sql query string that I can run via ActiveRecord::Base.connection.execute(my_query)

A SQL WHERE...IN clause expects an array of integer ids, something like this: "DELETE from contacts_tags where tag_id = 99 AND contact_id IN (1, 2, 3, 4)

I have an array of the ids I want to pass into the IN clause, but can't seem to format it in a way that SQL will accept.

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.

What is the proper way to pass a array of ids to a SQL WHERE clause?

Upvotes: 5

Views: 8143

Answers (2)

Rahul
Rahul

Reputation: 181

You can also use ANY operate with array values:

DELETE FROM contacts_tags WHERE tag_id = 99 AND contact_id = ANY('{ #{ids_for_delete.join(",")} }'::int[])

It will not break even if you pass an empty array.

Upvotes: 1

lei liu
lei liu

Reputation: 2775

Try:

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

use the method join to generate string like "1, 2, 3, 4.."

Upvotes: 17

Related Questions