Searching serialized data, using active record

I’m trying to do a simple query of a serialized column, how do you do this?

serialize :mycode, Array


1.9.3p125 :026 > MyModel.find(104).mycode
  MyModel Load (0.6ms)  SELECT `mymodels`.* FROM `mymodels` WHERE `mymodels`.`id` = 104 LIMIT 1
 => [43565, 43402] 
1.9.3p125 :027 > MyModel.find_all_by_mycode("[43402]")
  MyModel Load (0.7ms)  SELECT `mymodels`.* FROM `mymodels` WHERE `mymodels`.`mycode` = '[43402]'
 => [] 
1.9.3p125 :028 > MyModel.find_all_by_mycode(43402)
  MyModel Load (1.2ms)  SELECT `mymodels`.* FROM `mymodels` WHERE `mymodels`.`mycode` = 43402
 => [] 
1.9.3p125 :029 > MyModel.find_all_by_mycode([43565, 43402])
  MyModel Load (1.1ms)  SELECT `mymodels`.* FROM `mymodels` WHERE `mymodels`.`mycode` IN (43565, 43402)
 => [] 

  • BigDecimal can't be coerced into BigDecimal
  • Ember 2.7, Rails 5, JSONAPI, Active Model Serializers - counting the number of records in a hasMany relationship
  • How to add to a serialized array
  • active model serializer with virtual attribute - Rails 4
  • Rails 3 Serialization issue
  • Custom serialization for fields in Rails
  • Rails look up by serialized array
  • activerecord-postgres-hstore after save error ordering in Hash
  • 6 Solutions collect form web for “Searching serialized data, using active record”

    Basically, you can’t. The downside of #serialize is that you’re bypassing your database’s native abstractions. You’re pretty much limited to loading and saving the data.

    That said, one very good way to slow your application to a crawl could be:

    MyModel.all.select { |m| m.mycode.include? 43402 }
    

    Moral of the story: don’t use #serialize for any data you need to query on.

    It’s just a trick to not slow your application. You have to use .to_yaml.

    exact result:

    MyModel.where("mycode = ?", [43565, 43402].to_yaml)
    #=> [#<MyModel id:...]
    

    Tested only for MySQL.

    Serialized array is stored in database in particular fashion eg:

    [1, 2, 3, 4]
    in
    1\n 2\n 3\n etc
    

    hence the query would be

    MyModel.where("mycode like ?", "% 2\n%")
    

    put space between % and 2.

    Noodl’s answer is right, but not entirely correct.

    It really depends on the database/ORM adapter you are using: for instance PostgreSQL can now store and search hashes/json – check out hstore. I remember reading that ActiveRecord adapter for PostgreSQl now handles it properly. And if you are using mongoid or something like that – then you are using unstructured data (i.e. json) on a database level everywhere.

    However if you are using a db that can’t really handle hashes – like MySQL / ActiveRecord combination – then the only reason you would use serialized field is for somet data that you can create / write in some background process and display / output on demand – the only two uses that I found in my experience are some reports ( like a stat field on a Product model – where I need to store some averages and medians for a product), and user options ( like their preferred template color -I really don’t need to query on that) – however user information – like their subscription for a mailing list – needs to be searchable for email blasts.

    PostgreSQL hstore ActiveRecord Example:

    MyModel.where("mycode @> 'KEY=>\"#{VALUE}\"'")
    

    Good news! If you’re using PostgreSQL with hstore (which is super easy with Rails 4), you can now totally search serialized data. This is a handy guide, and here’s the syntax documentation from PG.

    In my case I have a dictionary stored as a hash in an hstore column called amenities. I want to check for a couple queried amenities that have a value of 1 in the hash, I just do

    House.where("amenities @> 'wifi => 1' AND amenities @> 'pool => 1'")
    

    Hooray for improvements!

    You can query the serialized column with a sql LIKE statement.

     MyModel.where("mycode LIKE '%?%'", 43402)
    

    This is quicker than using include?, however, you cannot use an array as the parameter.

    Ruby is the best programming language in the world - Ruby on Rails.