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.

check this article stackoverflow.com/questions/9814622/…

hiphapis
– hiphapis

2015-04-28 07:46:25 +00:00
Commented Apr 28, 2015 at 7:46 — hiphapis
– hiphapis, Commented Apr 28, 2015 at 7:46

frontendbeauty · Accepted Answer · 2012-04-01 08:59:11Z

3

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

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

answered Apr 1, 2012 at 8:59

frontendbeauty

2,1392 gold badges17 silver badges18 bronze badges

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

1 Comment

raymondralibi Over a year ago

it will also retrieves "catfish"

mikdiet · Accepted Answer · 2012-04-01 08:43:28Z

1

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

answered Apr 1, 2012 at 8:43

mikdiet

10.1k8 gold badges62 silver badges71 bronze badges

1 Comment

raymondralibi Over a year ago

for some reason, i prevent to add another model. it's ok for me, if there's no better solution. thx

Collectives™ on Stack Overflow

Rails active record query, serialized array

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related