Rails using ActiveRecord "where" when parameter takes an Array

Question

I am trying to use a rails "where" query using activeRecord. The query i have contains multiple conditions, one of which is an array:

User.where("state = ? AND salary >= ?", ["AL", "MI", "MA"], 1000)

The problem is that when I run it (either from a controller, or from a console), I don't get any errors, but what looks like an empty ActiveRecord object. If I have just one value in the array, it works fine. Its multiple values (that I know exist), that doesn't return the expected values.

SELECT `users`.* FROM `users` WHERE (salary >= 1000 AND state = "AL", "MI","MA")

I can use a hash instead, except I am not sure how to get all my conditions in the query in that regard. That, I can do the following:

User.where(state: ["AL", "MI", "MA"])

And that works, but not sure how to have the salary >= condition in there as well. Can anyone tell me what I am doing wrong, and how to resolve it?

Mischa · Accepted Answer · 2014-08-22 01:53:35Z

7

I'd prefer to use Mark's solution, but to make your initial attempt work, you have to change state = ? to state IN (?), like this:

User.where("state IN (?) AND salary >= ?", ["AL", "MI", "MA"], 1000)

This will generate a WHERE ... IN ('...', '...') query, which is what you need when passing an array.

The advantage of using a hash is that Rails automatically generates the correct SQL. It does matter if you pass a string or an array.

edited Aug 22, 2014 at 1:53

answered Aug 22, 2014 at 0:23

Mischa

43.4k9 gold badges101 silver badges111 bronze badges

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

1 Comment

Hawkeye001 Over a year ago

Thanks Mischa. The main reason I accepted your solution, even though Marks answer worked perfectly (and like you said, using a hash has its own advantages, as well as making it more readable), is that I am generating the query dynamically, so by having them all in the same string makes that generation easier.

Mark Thomas · Accepted Answer · 2014-08-21 23:59:14Z

4

Simply chain the where clauses:

User.where(state: ["AL", "MI", "MA"]).where("salary >= ?", 1000)

answered Aug 21, 2014 at 23:59

Mark Thomas

37.6k11 gold badges78 silver badges101 bronze badges

Collectives™ on Stack Overflow

Rails using ActiveRecord "where" when parameter takes an Array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related