Interpolating PG array in raw SQL query

Question

In a Rails (5.2) app with PG (10) as the database, I need to perform a raw SQL query.

In the query, I need to add aWHERE clause that checks the qp.id is among project.qp_ids which are stored as an array of strings.

t.text :qp_ids, array: true, default: []

I have tried several solutions, among which the following

" ... WHERE qp.id = ANY #{project.qp_ids}"
" ... WHERE qp.id = ANY #{project.qp_ids.join(', ')}"
" ... WHERE qp.id = ANY ARRAY(#{project.qp_ids.join(', '))}"
" ... WHERE qp.id = IN (#{project.qp_ids.join(', ')})"

But all produce a PG::SyntaxError.

What is the right way to interpolate a PG array?

UPDATE1

The code below works but is very ugly,

" ... WHERE qp.id = IN (#{self.quality_process_ids.map {|id| "'#{id}'"}.join(',')})"

What do you mean? As far as the query's concern the table has a row named id. — Sig
– Sig, Commented Oct 16, 2018 at 3:22
In your query you interpolate project.qp_ids as Ruby string, and qp.id as a column id of table qp. It's hard to say what you want to get. Are qp and products tables in PG? What are their fields? — Ilya Konyukhov
– Ilya Konyukhov, Commented Oct 16, 2018 at 3:28
qp.id is a column and project.qp_ids is the attribute qp_ids of the ActiveRecord project passed to the method responsible to build the query. Thanks. — Sig
– Sig, Commented Oct 16, 2018 at 3:30
Are you sure you need to do this with interpolation? ActiveRecord deals with things like where('id = any(array[?])', project.qp_ids) just fine. If you do need to build this by hand then you need to properly quote each element of qp_ids before you join them with commas and you'll need an extra set of parentheses (i.e. qp.id = any(array[...])). — mu is too short
– mu is too short, Commented Oct 16, 2018 at 3:46

Marcin Kołodziej · Accepted Answer · 2018-10-16 03:55:20Z

6

Wouldn't using IN be simpler?

" ... WHERE qp.id IN (#{project.qp_ids.join(',')})"

To deal with string quoting, you can use ActiveRecord's sanitization directly

your_model_instance.sanitize_sql_array(["project.qp_ids IN (?)", project.qp_ids])
 => "project.qp_ids IN ('foo','bar')"

to generate the condition which you may use in your WHERE clause.

edited Oct 16, 2018 at 3:55

answered Oct 16, 2018 at 3:22

Marcin Kołodziej

5,3231 gold badge12 silver badges18 bronze badges

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

5 Comments

Sig Over a year ago

Tried also that (forgot to add in my initial question). Unfortunately, it doesn't work. It reutrns WHERE qp.id IN (7EE488E9-20AA-4263-8F4A-3548999F77A6,7EE488E9-20AA-4263-8F4A-3548999F77A3)" but the values must be within '.

Marcin Kołodziej Over a year ago

Updated for strings, originally I assumed ids are integers.

Marcin Kołodziej Over a year ago

Removed the ugly version, kept the sanitization one then

Sig Over a year ago

Thanks, ActiveRecord's sanitization makes it look better.

mu is too short Over a year ago

You can still use the "= any" syntax if desired: ["qp.id = any(array[?])", project.qp_ids]. AFAIK PostgreSQL will convert in (list) to = any(array[list]) internally anyway. Anyway, this is the right approach.

Collectives™ on Stack Overflow

Interpolating PG array in raw SQL query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related