3

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.

Don P
  • 60,113
  • 114
  • 300
  • 432
  • possible duplicate of [Rails Console find users by array of ids](http://stackoverflow.com/questions/21435768/rails-console-find-users-by-array-of-ids) – Brad Werth Sep 22 '14 at 20:47
  • @BradWerth please read the note at the bottom of my question, thanks :) – Don P Sep 22 '14 at 20:58
  • I guess this isn't your actual code because it has syntax errors in it... – Mike Szyndel Sep 22 '14 at 21:28
  • Your where statement is missing the closing double quote. – Edmundo Ramirez-Semprit Sep 22 '14 at 21:31
  • I saw your note, did you see this answer? http://stackoverflow.com/a/21436165/525478 or http://stackoverflow.com/a/21435842/525478 or http://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 Sep 22 '14 at 21:49
  • It is covered in the docs for where, also, for future reference. http://apidock.com/rails/ActiveRecord/QueryMethods/where – Brad Werth Sep 22 '14 at 21:52

2 Answers2

11
User.where(is_awesome: true, id: acceptable_ids)
Kyle Decot
  • 20,715
  • 39
  • 142
  • 263
-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] )
SteveTurczyn
  • 36,057
  • 6
  • 41
  • 53
  • Argument conversion should certainly be handed over to ActiveRecord. Doing otherwise is bad practice as it makes SQL injections more probable, especially with beginners. – D-side Sep 22 '14 at 20:49
  • I think you have an extra parenthesis in there right? – Don P Sep 22 '14 at 20:54
  • Thanks @DonnyP ... I think I was missing a closing parenthesis, just added it. – SteveTurczyn Sep 22 '14 at 21:01
  • 1
    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... – Mike Szyndel Sep 22 '14 at 21:29
  • Thanks, @MichalSzyndel, always happy to improve. :) – SteveTurczyn Sep 22 '14 at 21:45
  • 1
    You're definitely opening yourself up to SQL injection here. – Kyle Decot Sep 23 '14 at 00:48
  • http://guides.rubyonrails.org/active_record_querying.html#pure-string-conditions you could fix this answer like `@users = User.where("is_awesome = true AND id IN ?",acceptable_ids.join(', '))`, but it would still not be as good as http://stackoverflow.com/a/25982790/525478 (or 3 of the answers in the question linked as a dupe) – Brad Werth Sep 23 '14 at 07:05