Search code examples
ruby-on-railsrails-activerecordpg

Using empty parameters in quoted_parameters(params)

I have the following code:

pg = ActiveRecord::Base.connection
res = pg.execute(query % quoted_parameters(params))

This is my pseudoquery:

select project_id from projects where client_id in (%{ids})

This is are the params:

{ :ids => [] }

It works fine when there are actual values in the ids array. However it doesn't seem capable of handling an empty array, I'm getting:

ERROR:  syntax error at or near ")"

Since it just puts in an empty string.

By default this works fine:

Project.where('client_id in (?)', [])

And generates the following query:

Project Load (3.5ms)  SELECT "projects".* FROM "projects"  WHERE (client_id in (NULL))

Is there a way to get the query % quoted_parameters(params) to generate the same query even if the array is empty?

Solution

  • Clarification: Are you trying to manually use the SQL in your app? Or asking how you can accomplish that with AR?

    With ActiveRecord you should be able to do:

    Project.where(client_id: ids)

    This will work in a number of situations:

    Project.where(client_id: [])
=> SELECT "projects".* FROM "projects" WHERE 1=0

Project.where(client_id: [1])
=> SELECT "projects".* FROM "projects" WHERE "projects"."client_id" = 1

Project.where(client_id: [1, 2])
=> SELECT "projects".* FROM "projects" WHERE "projects"."client_id" IN (1, 2)