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)

Arrays of id's or of objects? :)

mark
– mark

2010-08-06 20:45:15 +00:00
Commented Aug 6, 2010 at 20:45 — mark
– mark, Commented Aug 6, 2010 at 20:45
of ids. objects are too big =)

NullVoxPopuli
– NullVoxPopuli

2010-08-06 22:40:05 +00:00
Commented Aug 6, 2010 at 22:40 — NullVoxPopuli
– NullVoxPopuli, Commented Aug 6, 2010 at 22:40

Slobodan Kovacevic · Accepted Answer · 2010-08-06 20:48:37Z

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.

answered Aug 6, 2010 at 20:48

Slobodan Kovacevic

6,8983 gold badges32 silver badges33 bronze badges

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

1 Comment

NullVoxPopuli Over a year ago

They are ruby arrays. how do I do that?

nfm · Accepted Answer · 2010-08-06 23:15:23Z

0

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

answered Aug 6, 2010 at 23:15

nfm

20.9k17 gold badges65 silver badges94 bronze badges

Collectives™ on Stack Overflow

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

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