Rails: Using named_scope which triggers a MySQL "in"

Question

PROBLEM:

I want to run a query which would trigger something like

select * from users where code in (1,2,4);

using a named_scope.

---

WHAT I TRIED:

This is for a single code:

named_scope :of_code, lambda {|code| {:conditions => ["code = ?", code]}}

I tried something like

named_scope :of_codes, lambda {|codes| {:conditions => ["code in ?", codes]}}

and sent

user.of_codes('(1,2,4)')

it triggers select * from users where code in '(1,2,4)' which raises a MySQL error because of the extra quotes.

PS: Ideally I would like to send user.of_codes([1,2,4])

Answer 1

you can try follwing

named_scope :of_codes, lambda {|codes| {:conditions => ["code in "+codes]}}

and

user.of_codes('(1,2,4)')

EDITED For SQL INJECTION PROBLEM USE

named_scope :of_codes, lambda {|codes| {:conditions => ["code in (?) ", codes]}}

and

user.of_codes([1,2,4])

Answer 2

This will work just find and not expose you to the SQL injection attack:

named_scope :of_codes, lambda { |codes|
  { :conditions => ['code in (?)', codes] }
}

User.of_codes([1, 2, 3])
# executes "select * from users where code in (1,2,3)"

If you want to be a little more slick, you can do this:

named_scope :of_codes, lambda { |*codes|
  { :conditions => ['code in (?)', [*codes]] }
}

Then you can call it either with an Array (as above): User.of_codes([1, 2, 3]), or with a list of code arguments: User.of_codes(1, 2, 3).

Answer 3

The simplest approach would be to use a hash for conditions instead of an array:

named_scope :of_codes, lambda { |*codes| { :conditions => { :code => codes } } }

This will work as expected.

User.of_codes(1, 2, 3) # => SELECT ... code IN (1,2,3)
User.of_codes(1) # => SELECT ... code IN (1)