Ruby on Rails / MySQL: how do I do a search on an array using OR?
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 => ["(visibi开发者_如何学运维lity = ? && 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)
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.
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);
精彩评论