Ruby on Rails / MySQL: how do I do a search on an array using OR?

Question

Both privacy and users are arrays. I've taken out some logic to show the important stuff.

  named_scope :non_employees_and_beloning_to_users, lambda { |args|
    privacy = args[0]
    users = args[1]
    { :conditions =>  ["(visibility = ? && user_id = ?)", privacy, users] }
  }

but basically, the above generates the below, and when typed into a MySQL editor thing, no results show up. so, I came to believe this isn't the correct method for searching for stuff in mySOL with an array?

SELECT * 
FROM `proposals`  
WHERE visibility = '1,0' && user_id = '-1,8,9,11';

I want it to have the effect of

visibility = (1 or 0 ) AND user_id = (-1 or 8 or 9 or 11)

Answer 1

Since it seems that you have a comma separated list of values in visibility and users you could use MySQL's IN() function. Something like:

visibility IN (1,0) AND user_id IN (-1,8,9,11)

(see here for more info on IN(): http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in)

Alternatively you could convert visibility and users to Ruby arrays and then generate similar SQL code manually.

Answer 2

If args[0] and args[1] are arrays of ids, you just do this:

named_scope :non_employees_and_belonging_to_users, lambda { |args|
  privacy = args[0]
  users = args[1]
  { :conditions =>  ["visibility IN (?) AND user_id IN (?)", privacy, users] }
}

You need to wrap your ? placeholders in parens for SQL's IN to work. This should generate:

SELECT * FROM `proposals` WHERE visibility IN (1,0) AND user_id IN (-1,8,9,11);