execute_query with array as bind parameter

user_448934 0
Viewed 514

How do I use an Array (of Strings, or Integers) as bind parameter in select_all or exec_query for in where clauses?

# imagine a long a complicated SQL statement
# containing many CTEs and  
sql = <<~SQL
with things as (
  select ....
  where 
    x in ($1)
), morethings as (
  select
)

select 
sum(...), 
avg(...)
from morethings 
where categories in ($2)
SQL

binds = ??
ActiveRecord::Base.connection.exec_query(
  sql,
  "query name",
   binds
).to_a

I can generate binds for scalars like this:

  ActiveRecord::Relation::QueryAttribute.new("name", "Pascal", ActiveRecord::Type::String.new)

but don't know how to supply array values and it seems I am missing something.

Background: I want to run some raw SQL queries in a Rails project with the parameters being properly escaped. DB is Postgresql but I assume there is a DB agnostic way? Or do I have to use the raw connection.

The query is not related to a model and translating to Arel is possible but does not really fit the workflow.

postgresql activerecord ruby-on-rails
2 Answers
query = Arel::Table.new(:users).then do |u|
  users.project(users[Arel.star])
       .where(users[:id].in([1, 2, 3, 'a', 'b', 'c']))
end

query.to_sql
# => "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" IN (1, 2, 3, 'a', 'b', 'c')"
user_5448250

You can do this safely if you're willing to do a little bit of string manipulation pre-processing. We build a string that can be cast to a typed postgres array once it arrives at the db.

user_ids = [1,3,5,34]
user_id_pg_array = '{' + user_ids.join(',') + '}' # evaluates to '{1,3,5,34}'
ActiveRecord::Base.connection.exec_query(
  "SELECT status FROM notifications WHERE user_id = ANY($1::int[]),
  "my fav query",
  [ActiveRecord::Relation::QueryAttribute.new("user_id_pg_arr", user_id_pg_arr, ActiveRecord::Type::String.new)]
)

I'm no Ruby or Rails expert, so there may be a better way. I expected the db client library to handle this for me without thinking about it, so maybe there's a way to do that if you drop down to a postgres-specific gem.

user_15862290
Related