How to pass a list to an IN clause via a placeholder with Ruby Sequel

Question

I am trying to pass a quoted list to a placeholder in an IN clause with Ruby Sequel.

Specifically:

query = "
SELECT *
FROM PRICE
WHERE REGION IN (?)"

Then I use Ruby Sequel: dataset = dbr[query, param_values]; dataset.sql. If I pass param_values as a string like this: param_values = "'NSW1', 'VIC1'", the where clause becomes: WHERE REGION IN ((N'''NSW1'', ''VIC1''')) which doesn't work - presumably too many quotes. If I try passing the parameter as an array of quoted strings like this: param_values = %w[NSW1 VIC1] the where clause becomes: WHERE REGION IN ((N'NSW1' = N'VIC1')) which won't work.

Is there something that will work?

Answer 1

query = "
SELECT *
FROM PRICE
WHERE REGION IN ?"
regions = Sequel.lit("'NSW1', 'VIC1'")
sql = db.fetch(query, regions).sql
puts "sql: #{sql}"

Gives:

sql: SELECT *
FROM PRICE
WHERE REGION IN ('NSW1', 'VIC1')

which is what we want.

Answer 2

I would use Sequel's query builder language instead of writing SQL manually which would look like this if you used default naming conventions:

DB[:prices]
  .select(:date, :price, :region_id)
  .where(region_id: %w[NSW1 VIC1])