Using WHERE IN with Rails prepared statement syntax

Question

Let us say I have an array (received from the client side) of ids:

myArray = [1,5,19,27]

And I would like to return ALL items for which the (secondary) id is in that list.

In SQL this would be:

SELECT * FROM Items WHERE id IN (1,5,19,27)

I am aware that I could do:

Item.where(id: [1,5,9,27]),

however the longer where query that this would be tacked onto uses the prepared statement syntax Item.where('myAttrib = ? AND myOtherAttrib <> ? AND myThirdAttrib = ?', myVal[0], myVa[1], myVal[2])

with that in mind, what I would like is the following:

Item.where('id IN ?', myArray)

However, that produces a syntax error:

ActiveRecord::StatementInvalid: PG::Error: ERROR: syntax error at or near "1" LINE 1: SELECT "items".* FROM "items" WHERE (id in 1,2,3,4)

How can I work around this? What is the right way to use where with the prepared statement syntax for IN expressions.

Abraham P · Accepted Answer · 2013-09-30 17:57:56Z

19

I ended up using:

Item.where('id IN (?)', myArray)

answered Sep 30, 2013 at 17:57

Abraham P

15.6k14 gold badges64 silver badges131 bronze badges

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

Comments

mcfinnigan · Accepted Answer · 2013-09-29 08:38:19Z

1

You need to change your query to the following:

Item.where('id = ?', myArray)

ActiveRecord will then convert this to an IN clause if myArray is, in fact, an array.

answered Sep 29, 2013 at 8:38

mcfinnigan

11.7k38 silver badges28 bronze badges

3 Comments

Todd Over a year ago

I am pretty sure this is no longer the case (in Rails 5). This appears to produce the same issue as described in the O.P. Can you please provide a working example? I may be missing something.

hlascelles Over a year ago

Agree, this appears not to be the case, at least since Rails 4.2.

Kamilski81 Over a year ago

This gives a PG DatatypeMismatch, gotta use Item.where('id IN (?)', myArray) as mentioned by @Abraham P

br3nt · Accepted Answer · 2015-11-06 01:56:03Z

The issue with using raw sql is that the you loose the automatic namespacing of column names thats provided for free by Rails and AREL.

Using raw sql can start causing issues when combining and merging queries or scopes, or when doing joins.

No need to use raw sql to use IN:

Item.where(:id => myArray) # SELECT * FROM items WHERE id IN (...)

myArray can either be an array of values or an array of Items, or a single value.

Rails is smart enough to change the SQL to what you mean. Infact, if the array contains nil, the SQL will be SELECT * FROM items WHERE (id IS NULL or id IN (...)). This is clearly superior to using raw SQL which is static and only capable of dealing with a single use case.

If Rails doesn;t provide you with what you need, you can fall back to AREL. For example:

class Item scope :with_category, ->(categories) { where( arel_table[:category].in(categories) ) } scope :filter, ->(a, b, c) { not_b = arel_table[:myOtherAttrib].not_eq(b) where(:myAttrib => a, :myThirdAttrib => c).where(not_b) } end Item.with_category(['small', 'large']).filter(*myVal)

Collectives™ on Stack Overflow

Using WHERE IN with Rails prepared statement syntax

3 Answers 3

Comments

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Related