Ruby: How can I return all rows where an array column contains a given string?

Question

I'm working with some municipality data, and it's complicated by the fact that some municipalities are technically within multiple counties—which I handled in the database as an array, like so:

[<Municipality id: 11590, name: "Wisconsin Dells", county: ["Adams", "Columbia", "Juneau", "Sauk"], latitude: nil, longitude: nil, created_at: "2012-06-06 20:05:20", updated_at: "2012-06-06 20:05:20", municipality_type: "City">]

How can I construct a call in Ruby which would return all Municipalities where county contains a given string?

Ideally, this is how I'll list all cities in a given county, for example.

EDIT: I have serialize :county in my Municipality class.

You asked how to do it in Ruby, but do you mean in Rails? or do you want a pure Ruby solution? — Jesse Wolgamott
– Jesse Wolgamott, Commented Jun 6, 2012 at 20:35
I guess I preferred pure Ruby... but a Rails method would be fine. I just don't want to be messing with SQL. — jasonmklug
– jasonmklug, Commented Jun 6, 2012 at 20:37
@jsonmklug -- but after re-reading... this is a Rails ActiveModel class, right? And you want to be able to call Municipality.for_county("Columbia") and have it return all Municipality that match, right? — Jesse Wolgamott
– Jesse Wolgamott, Commented Jun 6, 2012 at 20:46
@JesseWolgamott You're right—that's exactly what I'm looking for. — jasonmklug
– jasonmklug, Commented Jun 6, 2012 at 20:48

Mori · Accepted Answer · 2012-06-06 20:49:14Z

2

class Muni < ActiveRecord::Base
  def self.for_county(county)
    where %Q[county like '%"#{county}"%']
  end
end

Or, slower, but if you "just don't want to be messing with SQL":

def self.for_county(county)
  all.select { |m| m.county.include? county }
end

edited Jun 6, 2012 at 20:49

answered Jun 6, 2012 at 20:31

Mori

27.8k10 gold badges71 silver badges74 bronze badges

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

8 Comments

fl00r Over a year ago

why do you need second select? And where("county like ?", '%"#{county}"%')

Emily Over a year ago

In case you're looking for the county "foo" and there's another county called "foobar" -- the SQL will match it, but the ruby select will filter it out.

fl00r Over a year ago

you could search with quotes, couldn't you?

fl00r Over a year ago

Also LIKE query won't use indexes in this case, what will cause performance issues

jasonmklug Over a year ago

When I say "don't want to mess with SQL", I mean I'd like it to be database-agnostic. If that's going to raise new problems, I guess I have to weigh the costs/benefits.

|

Collectives™ on Stack Overflow

Ruby: How can I return all rows where an array column contains a given string?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related