Writing SQL query WHERE.. IN.. anArray?

Rails
1

I have an array @dpt_ids

ex : @dpt_ids = [“43”, “48”, “49”, “50”, “51”]
I try to insert it into my sql query to be used in a ‘find_by_sql’

  query = "SELECT * , #{kms} AS km FROM cities"
  query << " WHERE department_id IN ( #{dpt_ids} ) "

but this transform my array into a unique string : ’ 4348495051’

I also tried

query << " WHERE department_id IN ( #{dpt_ids.each {|d| p d }} ) "

same…

How could I get the following resulting query :
WHERE department_id IN ( “43”, “48”, “49”, “50”, “51” ) ???

thansk for your help

Kad

2

And thinking of a nicer Rails-type way, try:

City.find(:all, :conditions => [“department_id IN (?)”, dpt_ids])

3

Instead of:

  query << " WHERE department_id IN ( #{dpt_ids} ) "

Try:

query << " WHERE department_id IN ( #{ dpt_ids.join(’,’) } ) "

4

query << " WHERE department_id IN ( #{dpt_ids.each {|d| p d }} ) "

same…

How could I get the following resulting query :
WHERE department_id IN ( “43”, “48”, “49”, “50”, “51” ) ???

query << " WHERE department_id IN ( “#{dpt_ids.join(’”,"’)}" )

Just make sure that dpt_ids really contains integers only and not
something malicious though.

5

Philip H. wrote:

query << " WHERE department_id IN ( #{dpt_ids.each {|d| p d }} ) "

same…

How could I get the following resulting query :
WHERE department_id IN ( “43”, “48”, “49”, “50”, “51” ) ???

query << " WHERE department_id IN ( “#{dpt_ids.join(’”,"’)}" )

Just make sure that dpt_ids really contains integers only and not
something malicious though.

It is best to just always use the [“WHERE department_id IN (?)”,
@dpt_ids] form. Along with being safer, it is much easier to read.

-matthew