mysql - 通过数据库序列化列进行搜索

Question

在 MySQL Rails 3 应用程序中，我的列之一被序列化为哈希。我的问题很简单:如何根据此列获得结果？

我尝试过这种方法:

example_hash = {id: 666}

ExampleTable.last.hash == example_hash
:true   ###### Here you can see that both hashes are the same

但是，像这样的查询没有给我任何结果，也没有异常(exception):

ExampleTable.where('hash LIKE ?', example_hash)

它只是没有检测到我需要的记录。

Answer 1

my_hash = {id:666}
search_term = my_hash.serializable_hash
ExampleTable.where(search_term)

我在现有的 Rails 4 应用程序中尝试过此操作。您必须测试它在 Rails 3 中是否有效。我的模型中没有 include ActiveModel::Serialization 但它仍然有效。

这是我的控制台 session :

Loading development environment (Rails 4.0.0)
$pry(main)> t = Tool.first
  Tool Load (10.9ms)  SELECT "tools".* FROM "tools" ORDER BY "tools"."id" ASC LIMIT 1
    => #<Tool id: 2, name: "6", serial: "134989652", purchased: nil, put_in_service: nil, cost:     nil, value: nil, in_service: true, retired: nil, created_at: "2013-07-17 06:12:59", updated_at: "2014-02-13 17:19:10", note: "", condition: "5 - Brand New Condition", old_location: "Sta 75", model: "Stihl 066", loaner: false, location_id: 9, category_id: 2, annual_service: false>
$pry(main)> x = t.serializable_hash
=> {"id"=>2,
 "name"=>"6",
 "serial"=>"134989652",
 "purchased"=>nil,
 "put_in_service"=>nil,
 "cost"=>nil,
 "value"=>nil,
 "in_service"=>true,
 "retired"=>nil,
 "created_at"=>Tue, 16 Jul 2013 23:12:59 PDT -07:00,
 "updated_at"=>Thu, 13 Feb 2014 09:19:10 PST -08:00,
 "note"=>"",
 "condition"=>"5 - Brand New Condition",
 "old_location"=>"Sta 75",
 "model"=>"Stihl 066",
 "loaner"=>false,
 "location_id"=>9,
 "category_id"=>2,
 "annual_service"=>false}
$pry(main)> Tool.where(x)
  Tool Load (10.3ms)  SELECT "tools".* FROM "tools" WHERE "tools"."id" = 2 AND "tools"."name" = '6' AND "tools"."serial" = '134989652' AND "tools"."purchased" IS NULL AND "tools"."put_in_service" IS NULL AND "tools"."cost" IS NULL AND "tools"."value" IS NULL AND "tools"."in_service" = 't' AND "tools"."retired" IS NULL AND "tools"."created_at" = '2013-07-17 06:12:59.638381' AND "tools"."updated_at" = '2014-02-13 17:19:10.674233' AND "tools"."note" = '' AND "tools"."condition" = '5 - Brand New Condition' AND "tools"."old_location" = 'Sta 75' AND "tools"."model" = 'Stihl 066' AND "tools"."loaner" = 'f' AND "tools"."location_id" = 9 AND "tools"."category_id" = 2 AND "tools"."annual_service" = 'f'
=> [#<Tool id: 2, name: "6", serial: "134989652", purchased: nil, put_in_service: nil, cost: nil, value: nil, in_service: true, retired: nil, created_at: "2013-07-17 06:12:59", updated_at: "2014-02-13 17:19:10", note: "", condition: "5 - Brand New Condition", old_location: "Sta 75", model: "Stihl 066", loaner: false, location_id: 9, category_id: 2, annual_service: false>]