CODEWITHSUNDEEP
ruby-on-railspostgresql
Phil Frost
Phil Frost

Reputation: 3966

Executing a raw SQL query in Rails with an array parameter against PostgreSQL

I would have hoped this would work:

user_ids = [1, 154, 31908]
query = "SELECT id FROM users WHERE id = ANY (ARRAY[$1])"
ActiveRecord::Base.connection.exec_query(query, "SQL", [[nil, user_ids]])

However this results in the exception TypeError: can't cast Array.

It seems Rails can handle an array parameter when using the where method of some model but is there any way to have an array parameter when making a raw SQL query, not involving ActiveRecord (except to get the connection) or any model?

Upvotes: 7

Views: 5740

Answers (4)

niinyarko
niinyarko

Reputation: 524

The following worked for me.

 user_ids = [1, 2, 3].to_s
 user_ids = user_ids.gsub("[", "{")
 user_ids = user_ids.gsub("]", "}")
 query = "SELECT id FROM users WHERE id = ANY ($1)"

 ActiveRecord::Base.connection.exec_query(query, "SQL", [[nil, user_ids]])

Upvotes: -1

Boris B.
Boris B.

Reputation: 5024

First create a reusable composite type definition that describes an Array of Integers:

# Somewhere in the static context
IntegerArray = ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Array.new(ActiveModel::Type::Integer.new).freeze

Then use this type in queries:

user_ids = [1, 154, 31908]
query = "SELECT id FROM users WHERE id = ANY($1)"
params = IntegerArray.serialize(user_ids)
ActiveRecord::Base.connection.exec_query(query, "MyLogLabel", [[nil, params]])

Note that there's no need to use PG::TextEncoder directly nor PG::Connection#exec_params. There's still a need to use ActiveRecord::ConnectionAdapters::PostgreSQL since composite types are not backend-agnostic.

Upvotes: 3

Phil Frost
Phil Frost

Reputation: 3966

Not pretty, but it's possible to get a plain PG::Connection connection like so:

connection = ActiveRecord::Base.connection.instance_variable_get(:@connection)

Unfortunately, the pg gem doesn't very elegantly support arrays either, so this only gets you halfway there, and you have to do some rather verbose, manual encoding of the array:

user_ids = [1, 154, 31908]
query = "SELECT id FROM users WHERE id = ANY ($1)"
params = [PG::TextEncoder::Array.new.encode(user_ids)]
connection.exec_params(query, params)

Depending on how pretty you want to make this, you could monkeypatch ActiveRecord::ConnectionAdapters::PostgreSQLAdapter to expose @connection, or subclass it and configure Rails to use that subclass. Also, the pg gem supports customizing typecasting behavior, which would reduce some of the tedium.

Upvotes: 1

hschne
hschne

Reputation: 704

Instead of using the array directly, you will have to convert it to a string if you want to use parameter bindings:

user_ids = [1,154,31908]
user_ids = user_ids.join(',')
query = "SELECT id FROM users WHERE id = ANY (ARRAY[$1])"
ActiveRecord::Base.connection.exec_query(query, "SQL", [[nil, user_ids]])

This results in the query SELECT id FROM users WHERE id = ANY (ARRAY[1,154,31908]).

If you are just goofing around, and trying out queries, you could also ditch bindings and use string interpolation directly:

query = "SELECT id FROM users WHERE id = ANY (ARRAY[#{user_ids.join(',')}])"

Keep in mind that this would open you up for SQL Injection and similar nasties, so I do not recommend this approach for anything that is accessible from the outside.

Upvotes: 0

Related Questions