CODEWITHSUNDEEP
mysqlruby-on-rails-3
nullnullnull
nullnullnull

Reputation: 8189

Rails: Querying with an Array

I have the following method which creates and then executes an SQL query:

def edition_authors(edition, authors)
  query_string = "contributor_type = ? AND author = ?"
  for i in 1..authors.length - 1
    query_string += " OR author = ?"
  end
  return edition.contributors.where(query_string, 'Author', authors)
end

The last line is the one I'm having trouble with. I want the 'authors' array to somehow turn into a set of strings. For instance, if the authors array contained ['James Joyce', 'Cory Doctorow', 'Cormac McCarthy'], I'd like that last line to read like:

return edition.contributors.where(query_string, 'Author', 'James Joyce', 'Cory Doctorow', 'Cormac McCarthy')

How could I accomplish this?

Upvotes: 3

Views: 4536

Answers (2)

Tom Harrison
Tom Harrison

Reputation: 14018

Try this:

  1. Instead of using a big bunch of ORs, use a SQL IN clause, and
  2. then use Array#join to supply the values

So: 

.where("contributor_type = ? AND author IN (?)", 'Author', authors.join("','"))

should do the trick, with some caveats: string values in a SQL IN clause need to be single quoted and comma separated SELECT * FROM fubar WHERE blah IN ('foo','bar','baz'); I think Rails is clever about knowing how to quote things, so look in the Rails log to see what SQL is being generated if you're getting an error.

Also, since your values may contain single quotes (Georgia O'Keefe) I am not sure if Rails is smart enough to escape these for you when used in this manner.

Bonus tip: make sure there's an index on the Author column. This is an expensive query, in general.

Upvotes: 2

Michael
Michael

Reputation: 10474

Depends which rails and which ruby you are using,

  1. ActiveRecord already has this functionality in Rails 3: Model.where(:my_field => ['Author', 'James Joyce', 'Cory Doctorow', 'Cormac McCarthy'])

  2. arr.map{|v| v.to_s.inspect} will get you a comma separated list

  3. arr.join(',') will give you a comma separated list, no quotes.

Then you can use this string however you wish.

Upvotes: 6

Related Questions