CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecord
Chris Hawkins
Chris Hawkins

Reputation: 658

Querying arrays stored as comma separated strings in active record

I have to search on many associated models through a has_many relationship. For argument's sake, let's say I have a Product model with many categories through a has_many association. Because I have to do this with many of the parent model's child associations, I've created a cache table, where I store all the id's in the association as a comma separated string - '23,34,56,78' rather than doing a join. This also helps with advanced searches I need to do later with conditional ANDs on the category ids.

My question is what is the best way to query the presence of an id in such a string in active record. If for example, I want all parent models with associations of ids 34, 56 and 78.

To accurately search on one id I have to search for '%,34,%', '34,%', '%,34' to account for different positions in the string an id can occupy. Is there an easier way?

Upvotes: 0

Views: 1256

Answers (1)

Matt
Matt

Reputation: 14038

I don't know how fast this would be but you can split the string by commas to create an array then do a .includes?() on said array... But is this really any faster than SQL?

value.split(',').includes?(other_value)

or with multiple items:

value.split(',') && [id1, id2, id3]

This will return a list of ids that appear in both arrays.

Upvotes: 0

Related Questions