1

I have one table called posts

create_table "posts", force: :cascade do |t|
   t.text     "values"
end

My record values are

#<Post id: 1, values: {"p1"=>"1", "p2"=>"xyz"}>
#<Post id: 2, values: {"p1"=>"1", "p2"=>"abc"}>

I want to find record from post where values[:p2] = "abc"

Nermin
  • 6,118
  • 13
  • 23
ashvin
  • 2,020
  • 1
  • 16
  • 33
  • Why are you storing hash values in your DB? Why not create two columns, one for `p1` and one for `p2`. Then you can query it with `posts.where(p2: 'abc')`. – nicholas79171 Jul 21 '16 at 13:46
  • Wait.. what? The values are a JSON but stored as a text? that is really ugly and unusable. do these p1, p2.. need to be generic? and why don't you use `t.json`? – siegy22 Jul 21 '16 at 13:51
  • Rails also have datatype to to store hash values it is :hstore, you can use it like t.hstore. – Navin Jul 21 '16 at 13:55
  • The answer depends on which database you're using. There also might not be a *good* answer. – Azolo Jul 21 '16 at 14:31
  • @nicholas79171 you are right But if i want to find using hash value then what will be the solution – ashvin Jul 21 '16 at 14:47
  • @Azolo database is postgres – ashvin Jul 21 '16 at 14:59

1 Answers1

1

To answer your question, your best bet is using the LIKE query to get columns that contain your substring:

Posts.where("values like %\"p2\"=>\"abc\"%")

The above query has not been tested, but should get the rows that contain "p2"=>"abc".

But as I mentioned in the comments, I do not recommend this solution. Configuring your table like so would be much better:

create_table "posts", force: :cascade do |t|
  t.text     "p1"
  t.text     "p2"
end

Then your new query becomes

Posts.where(p2: 'abc')

which is not only much cleaner, it will be much quicker.

Community
  • 1
  • 1
nicholas79171
  • 1,203
  • 2
  • 15
  • 28