Rails: Querying with an Array

Question

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?

Michael · Accepted Answer · 2012-11-15 01:50:50Z

6

Depends which rails and which ruby you are using,

ActiveRecord already has this functionality in Rails 3: Model.where(:my_field => ['Author', 'James Joyce', 'Cory Doctorow', 'Cormac McCarthy'])
arr.map{|v| v.to_s.inspect} will get you a comma separated list
arr.join(',') will give you a comma separated list, no quotes.

Then you can use this string however you wish.

answered Nov 15, 2012 at 1:50

Michael

10.5k1 gold badge39 silver badges51 bronze badges

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

Comments

Tom Harrison · Accepted Answer · 2012-11-15 01:58:17Z

2

Try this:

Instead of using a big bunch of ORs, use a SQL IN clause, and
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.

answered Nov 15, 2012 at 1:58

Tom Harrison

14.2k3 gold badges53 silver badges81 bronze badges

3 Comments

Tom Harrison Over a year ago

Aw snap! @Michael's answer is much better than mine. Rails is so cool, of course there's a simple way :-).

Michael Over a year ago

your join is missing the beginning and end single quotes, you would need to add them in.

Tom Harrison Over a year ago

Actually, that was "hopefully intentional" which is what I was implying by "Rails is clever..." -- you don't need to quote strings when used in a placeholder. But yeah, if I am wrong, the IN clause would be ...IN ('?')... or maybe the value would need to be something horrific like "'#{authors.join("','")}'", which leads me to my comment, that your answer is better all around :-)

Collectives™ on Stack Overflow

Rails: Querying with an Array

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related