Get all Rails records from an array of IDs

Question

I have an array of record IDs ["303", "430", "4321", "5102"]. I want to get all records that match these IDs, using SQL:

acceptable_ids = ["303", "430", "4321", "5102"]
@users = User.where("is_awesome = true AND id IN acceptable_ids)

Gives this error:

ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "["

What is the correct way to write my query to get all users with ids that match acceptable_ids?

Note:

I am aware of User.find(acceptable_ids), but can't use this since I am constructing a SQL query with select, where, and join clauses.

possible duplicate of Rails Console find users by array of ids — Brad Werth
– Brad Werth, Commented Sep 22, 2014 at 20:47
@BradWerth please read the note at the bottom of my question, thanks :) — Don P
– Don P, Commented Sep 22, 2014 at 20:58
I guess this isn't your actual code because it has syntax errors in it... — Mike Szyndel
– Mike Szyndel, Commented Sep 22, 2014 at 21:28
I saw your note, did you see this answer? stackoverflow.com/a/21436165/525478 or stackoverflow.com/a/21435842/525478 or stackoverflow.com/a/25983580/525478 , all of which use where... That is one of the reasons I picked that question, out of the dozen or so on the site... — Brad Werth
– Brad Werth, Commented Sep 22, 2014 at 21:49

Kyle Decot · Accepted Answer · 2014-09-22 20:41:28Z

12

User.where(is_awesome: true, id: acceptable_ids)

answered Sep 22, 2014 at 20:41

Kyle Decot

20.9k40 gold badges151 silver badges270 bronze badges

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

Comments

SteveTurczyn · Accepted Answer · 2014-09-23 07:54:06Z

-3

You could do this.

@users = User.where("is_awesome = true AND id IN (#{acceptable_ids.join(', ')})")

I'm sure I've seen a simpler way but can't recall it at the moment... but above should work.

EDIT

However, you can see from the vociferous comments that this is not a popular answer, so you should look at some of the alternatives linked and listed, e.g.

# Will raise exception if any value not found
User.find( [1,3,5] )

# Will not raise an exception
User.find_all_by_id( [1,3,5] )

edited Sep 23, 2014 at 7:54

answered Sep 22, 2014 at 20:47

SteveTurczyn

37k6 gold badges46 silver badges56 bronze badges

7 Comments

D-side Over a year ago

Argument conversion should certainly be handed over to ActiveRecord. Doing otherwise is bad practice as it makes SQL injections more probable, especially with beginners.

Don P Over a year ago

I think you have an extra parenthesis in there right?

SteveTurczyn Over a year ago

Thanks @DonnyP ... I think I was missing a closing parenthesis, just added it.

Mike Szyndel Over a year ago

This answer is so bad I have to down vote it. Please look at the other answer to see how it should be done. You should never concatenate SQL queries by hand (possible SQL injection), and you should use what Rails gives you instead of reinventing the wheel...

Kyle Decot Over a year ago

You're definitely opening yourself up to SQL injection here.

|

Collectives™ on Stack Overflow

Get all Rails records from an array of IDs

2 Answers 2

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related