Why does query with AND and OR operators yield unexpected results?

I have this query but it's not giving me the expected results: User.all(:conditions => ["company_id == ? AND role_id == ? OR role_id == ? OR role_id == ?", X, 1,2,3 ])

It's supposed to mean: Get all the users with X co开发者_开发知识库mpany_id AND their roles can be either 1 or 2 or 3.

So it can be either of those roles, but ALL of those users have to be from THAT company_id.

---

It isn't working because the precedence is wrong. That is,

company_id = ? AND role_id = ? OR role_id = ? OR role_id = ?

is interpreted as

((company_id = ? AND role_id = ?) OR role_id = ?) OR role_id = ?

because AND has higher precedence than OR -- but more relevant to this case -- they are also both are leftward-associative.

The simple fix would thus be:

company_id = ? AND (role_id = ? OR role_id = ? OR role_id = ?)

(But see other answers for an alternative syntax and/or approaches. I also took the liberty of fixing the equality operator.)

Happy coding.

---

Could be a couple things.

First use single equals =, not double equals == in :conditions => "...".

Second, you could either use "...AND role_id IN (?,?,?)", X, 1, 2, 3]), or you need to group your role_id comparisons with parenthesis so it would be condition AND (condition OR condition OR condition).

---

You may want to investigate https://github.com/binarylogic/searchlogic

However otherwise you could say

User.all(:conditions => ["company_id = ? AND role_id IN (?,?,?)", X, 1,2,3 ])

---

For future reference, this helped me a lot ease up this kind of finds, you could do:

company_id = "X"
role_ids = [1,2,3]
User.find(:all, :conditions => "company_id=#{company_id} AND role_id IN (#{role_ids.join(',')})")