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

Question

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 company_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.

Answer 1

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.

Answer 2

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).