Rails active record query, serialized array

Question

Suppose I have Users data that store array of pet in String datatype

[
  #<User id: 1, name: "John", pets: "---\n- cat\n- dog\n- bunny\n- ''\n">,
  #<User id: 2, name: "Pete", pets: "---\n- dog\n- bunny\n- ''\n">,
  #<User id: 3, name: "Jack", pets: "---\n- cat\n- ''\n">,
  #<User id: 4, name: "Kurt", pets: "---\n- cat\n- bunny\n- ''\n">
]

Can i get all users that has a cat? Maybe something like User.find_all_by... or User.where(....) or anything that return as a relation? So i can order with active record query.

I know i can get all users that has a cat with

User.all.select{|s| YAML.load(s.pets).include?'cat'}

, but it convert to array that cannot be ordered with active record query.

thx for helping.

Answer 1

You could use simple SQL to see if 'cat' shows up in the serialized column.

User.where('pets LIKE "%cat%"').all

Answer 2

You need to normalize your data, add Pet model and set has_and_belongs_to_many association between theese models.