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

Question

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?

lei liu · Accepted Answer · 2016-04-19 00:34:59Z

17

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.."

answered Apr 19, 2016 at 0:34

lei liu

2,7851 gold badge18 silver badges18 bronze badges

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

3 Comments

Jim Over a year ago

Thanks! I must have been overthinking it.

rony36 Over a year ago

when ids_for_delete is blank, it's going to break

lei liu Over a year ago

@rony36 you will need to detect the array first, make sure it's not blank before you use this express.

Rahul · Accepted Answer · 2021-02-15 08:31:34Z

1

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.

answered Feb 15, 2021 at 8:31

Rahul

1811 gold badge3 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related