CODEWITHSUNDEEP
sqlruby-on-rails-3activerecord
ryan0
ryan0

Reputation: 1525

ActiveRecord find by model's array attribute containing a value (not association array)

I have a Rails model with an attribute that is an array of data values (strings). I would like to do an ActiveRecord find that returns any records whose array contains a specific value. Is there a way to do this with ActiveRecord that would make it more performant than iteration?

Upvotes: 3

Views: 4167

Answers (1)

Dylan Markow
Dylan Markow

Reputation: 124419

Since Rails serializes arrays using YAML by default, it's difficult to do a "contains a value" query.

If your values are unique and part of a fixed set, (e.g. a roles column with ['User', 'Admin', 'Guest']), you could do something like this:

User.where("roles ilike '%\n- Guest\n%'")

since that will match on the YAML of ---\n- User\n- Admin\n Guest\n.

Alternatively, you could use the native array type in Postgres. In Rails 3, you would need to use the activerecord-postgres-array gem (though I don't know how well maintained this is); in Rails 4, it's supported natively.

You would set up your column as such:

t.string :roles_array, :array => true, :length => 30

Then you could query against it:

User.where("'Guest' = ANY(roles_array)")

Upvotes: 11

Related Questions