Rails query objects by key value of hash saved to column?

Question

I have 2 objects, Visitors and Events. Visitors have multiple Events. An event stores parameters like this...

#<Event id: 5466, event_type: "Visit", visitor_token: "c26a6098-64bb-4652-9aa0-e41c214f42cb", contact_id: 657, data: {"url"=>"http://widget.powerpress.co/", "title"=>"Home (light) | Widget"}, created_at: "2015-12-17 14:51:53", updated_at: "2015-12-17 14:51:53", website_id: 2>

As you can see, there is a serialized text column called data that stores a hash with more data.

I need to find out if a visitor has visited a certain page, which would be very simple if the url parameter were it's own column, or if the hash were an hstore column, however it wasn't originally set up that way and it's a part of the saved hash.

Here's my attempted rails queries...

visitor.events.where("data -> url = :value", value: 'http://widget.powerpress.co/')

visitor.events.where("data like ?", "{'url' => 'http://widget.powerpress.co/'}")

visitor.events.where("data -> :key LIKE :value", :key => 'url', :value => "%http://widget.powerpress.co/%")

How does one properly query postgres to find objects that have a hash that contains a key with a specific value?

the data column is a String ?

Baldrick
– Baldrick

2015-12-17 16:19:10 +00:00
Commented Dec 17, 2015 at 16:19 — Baldrick
– Baldrick, Commented Dec 17, 2015 at 16:19

Baldrick · Accepted Answer · 2015-12-17 16:41:12Z

1

I suspect you're not looking for the right string. It should be "url"=>"http://widget.powerpress.co/", so:

visitor.events.where("data like ?", '%"url"=>"http://widget.powerpress.co/"%')

Check the right value directly in DB.

answered Dec 17, 2015 at 16:41

Baldrick

24.4k6 gold badges76 silver badges80 bronze badges

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

3 Comments

user2101461 Over a year ago

While this runs it doesn't return anything (there are for sure events with that url btw). I can successfully run Event.where("data like ?", '%http://widget.powerpress.co/%') however this includes events with urls like /blog etc. Any work around?

Baldrick Over a year ago

Can you add to your post an actual value of the column data from the DB ? You posted an exemple from Rails, but the format may be different in DB.

user2101461 Over a year ago

Event.last.attributes_before_type_cast returns

{"id"=>"5466", "event_type"=>"View", "visitor_token"=>"c26a6098-64bb-4652-9aa0-e41c214f42cb", "contact_id"=>"657", "data"=>{"url"=>"http://widget.powerpress.co/", "title"=>"Home (light) | Widget", "page"=>"/", "time"=>"1450363908.892", "contactable_name"=>"overlay theme 8"}, "created_at"=>"2015-12-17 14:51:53.9496", "updated_at"=>"2015-12-17 14:51:53.9496", "website_id"=>"2", "tag_id"=>nil, "contactable_type"=>"Overlay", "contactable_id"=>"87", "visitor_id"=>"117", "broadcast_id"=>nil, "flow_id"=>"7", "stage_id"=>nil, "goal_id"=>"5", "url"=>nil}

Veets · Accepted Answer · 2015-12-17 18:30:26Z

0

If you are storing hash in a text column, try following:

visitor.events.select{|ve| eval(ve.data)["url"] == "http://widget.powerpress.co/"}

Hope, it helps!

answered Dec 17, 2015 at 18:30

Veets

1716 bronze badges

2 Comments

user2101461 Over a year ago

TypeError: no implicit conversion of Hash into String

Veets Over a year ago

Interesting. Please post the output of Event from rails console.

Xeeshan · Accepted Answer · 2016-06-09 06:01:39Z

0

It worked for me.

 visitor.events.select { |n| n.data && n.data['url'] == "http://widget.powerpress.co/"}

answered Jun 9, 2016 at 6:01

Xeeshan

15610 bronze badges

Collectives™ on Stack Overflow

Rails query objects by key value of hash saved to column?

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related