New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Querying Events by properties hash key/value pair #120

Closed
campgurus opened this Issue Aug 13, 2015 · 17 comments

Comments

8 participants
@campgurus

campgurus commented Aug 13, 2015

I have been looking but haven't found an answer to this seemingly simple question. I am trying to search events to return number of article views for @Article. so I am guessing it should be something like:

Ahoy::Event.where("properties @> (:key => :value)", :key => 'article_id', :value => @article.id).count

I have also looked at the possibility of using hstore, but I am not sure if that is necessary. Any guidance would be great. Thanks.

@ankane

This comment has been minimized.

Owner

ankane commented Aug 13, 2015

jsonb, hstore, and json column types should all be queryable. If you're on Postgres 9.4, jsonb would be my choice; otherwise, hstore.

@campgurus

This comment has been minimized.

campgurus commented Aug 13, 2015

Getting an error with the new datatype from this line in my controller:

ahoy.track "Viewed Article", title: "#{@article.subjects.first.name}", article_id: @article.id, user_id: current_user.try(:id)

Error Message: TypeError in ArticlesController#show no implicit conversion of Hash into String

Full trace:

.bundle/gems/ruby/2.2.0/gems/json-1.8.2/lib/json/common.rb:155:in initialize' .bundle/gems/ruby/2.2.0/gems/json-1.8.2/lib/json/common.rb:155:innew'
.bundle/gems/ruby/2.2.0/gems/json-1.8.2/lib/json/common.rb:155:in parse' .bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/json/decoding.rb:26:indecode'
.bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/coders/json.rb:9:in load' .bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/type/serialized.rb:19:intype_cast_from_database'
.bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/type/mutable.rb:5:in type_cast_from_user' .bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/attribute.rb:110:intype_cast'
.bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/attribute.rb:42:in original_value' .bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/attribute.rb:37:invalue'
.bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/attribute.rb:50:in changed_from?' .bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/attribute_methods/dirty.rb:141:in_field_changed?'
.bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/attribute_methods/dirty.rb:114:in save_changed_attribute' .bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/attribute_methods/dirty.rb:98:inwrite_attribute'
.bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/attribute_methods.rb:50:in __temp__0727f607562747965637' .bundle/gems/ruby/2.2.0/gems/ahoy_matey-1.1.1/lib/ahoy/stores/active_record_store.rb:33:inblock in track_event'
.bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/core.rb:283:in initialize' .bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/inheritance.rb:61:innew'
.bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/inheritance.rb:61:in new' .bundle/gems/ruby/2.2.0/gems/ahoy_matey-1.1.1/lib/ahoy/stores/active_record_store.rb:28:intrack_event'
.bundle/gems/ruby/2.2.0/gems/ahoy_matey-1.1.1/lib/ahoy/tracker.rb:19:in track' app/controllers/articles_controller.rb:41:inshow'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_controller/metal/implicit_render.rb:4:in send_action' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/abstract_controller/base.rb:198:inprocess_action'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_controller/metal/rendering.rb:10:in process_action' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/abstract_controller/callbacks.rb:20:inblock in process_action'
.bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:117:in call' .bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:117:incall'
.bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:555:in block (2 levels) in compile' .bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:505:incall'
.bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:505:in call' .bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:92:in_run_callbacks'
.bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:776:in _run_process_action_callbacks' .bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:81:inrun_callbacks'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/abstract_controller/callbacks.rb:19:in process_action' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_controller/metal/rescue.rb:29:inprocess_action'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_controller/metal/instrumentation.rb:32:in block in process_action' .bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/notifications.rb:164:inblock in instrument'
.bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/notifications/instrumenter.rb:20:in instrument' .bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/notifications.rb:164:ininstrument'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_controller/metal/instrumentation.rb:30:in process_action' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_controller/metal/params_wrapper.rb:250:inprocess_action'
.bundle/gems/ruby/2.2.0/gems/searchkick-0.8.7/lib/searchkick/logging.rb:107:in process_action' .bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/railties/controller_runtime.rb:18:inprocess_action'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/abstract_controller/base.rb:137:in process' .bundle/gems/ruby/2.2.0/gems/actionview-4.2.1/lib/action_view/rendering.rb:30:inprocess'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_controller/metal.rb:196:in dispatch' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_controller/metal/rack_delegation.rb:13:indispatch'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_controller/metal.rb:237:in block in action' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/routing/route_set.rb:74:incall'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/routing/route_set.rb:74:in dispatch' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/routing/route_set.rb:43:inserve'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/journey/router.rb:43:in block in serve' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/journey/router.rb:30:ineach'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/journey/router.rb:30:in serve' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/routing/route_set.rb:819:incall'
.bundle/gems/ruby/2.2.0/gems/rack-pjax-0.8.0/lib/rack/pjax.rb:12:in call' .bundle/gems/ruby/2.2.0/gems/bullet-4.14.7/lib/bullet/rack.rb:12:incall'
.bundle/gems/ruby/2.2.0/gems/warden-1.2.3/lib/warden/manager.rb:35:in block in call' .bundle/gems/ruby/2.2.0/gems/warden-1.2.3/lib/warden/manager.rb:34:incatch'
.bundle/gems/ruby/2.2.0/gems/warden-1.2.3/lib/warden/manager.rb:34:in call' .bundle/gems/ruby/2.2.0/gems/rack-1.6.0/lib/rack/etag.rb:24:incall'
.bundle/gems/ruby/2.2.0/gems/rack-1.6.0/lib/rack/conditionalget.rb:25:in call' .bundle/gems/ruby/2.2.0/gems/rack-1.6.0/lib/rack/head.rb:13:incall'
.bundle/gems/ruby/2.2.0/gems/remotipart-1.2.1/lib/remotipart/middleware.rb:27:in call' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/middleware/params_parser.rb:27:incall'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/middleware/flash.rb:260:in call' .bundle/gems/ruby/2.2.0/gems/rack-1.6.0/lib/rack/session/abstract/id.rb:225:incontext'
.bundle/gems/ruby/2.2.0/gems/rack-1.6.0/lib/rack/session/abstract/id.rb:220:in call' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/middleware/cookies.rb:560:incall'
.bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/query_cache.rb:36:in call' .bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:649:incall'
.bundle/gems/ruby/2.2.0/gems/activerecord-4.2.1/lib/active_record/migration.rb:378:in call' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/middleware/callbacks.rb:29:inblock in call'
.bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:88:in call' .bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:88:in_run_callbacks'
.bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:776:in _run_call_callbacks' .bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/callbacks.rb:81:inrun_callbacks'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/middleware/callbacks.rb:27:in call' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/middleware/reloader.rb:73:incall'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/middleware/remote_ip.rb:78:in call' .bundle/gems/ruby/2.2.0/gems/rollbar-1.5.3/lib/rollbar/middleware/rails/rollbar.rb:24:inblock in call'
.bundle/gems/ruby/2.2.0/gems/rollbar-1.5.3/lib/rollbar.rb:799:in scoped' .bundle/gems/ruby/2.2.0/gems/rollbar-1.5.3/lib/rollbar/middleware/rails/rollbar.rb:22:incall'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/middleware/debug_exceptions.rb:17:in call' .bundle/gems/ruby/2.2.0/gems/rollbar-1.5.3/lib/rollbar/middleware/rails/show_exceptions.rb:22:incall_with_rollbar'
.bundle/gems/ruby/2.2.0/gems/web-console-2.1.2/lib/web_console/middleware.rb:37:in call' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/middleware/show_exceptions.rb:30:incall'
.bundle/gems/ruby/2.2.0/gems/railties-4.2.1/lib/rails/rack/logger.rb:38:in call_app' .bundle/gems/ruby/2.2.0/gems/railties-4.2.1/lib/rails/rack/logger.rb:20:inblock in call'
.bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/tagged_logging.rb:68:in block in tagged' .bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/tagged_logging.rb:26:intagged'
.bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/tagged_logging.rb:68:in tagged' .bundle/gems/ruby/2.2.0/gems/railties-4.2.1/lib/rails/rack/logger.rb:20:incall'
.bundle/gems/ruby/2.2.0/gems/ahoy_matey-1.1.1/lib/ahoy/engine.rb:20:in call_with_quiet_ahoy' .bundle/gems/ruby/2.2.0/gems/request_store-1.1.0/lib/request_store/middleware.rb:8:incall'
.bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/middleware/request_id.rb:21:in call' .bundle/gems/ruby/2.2.0/gems/rack-1.6.0/lib/rack/methodoverride.rb:22:incall'
.bundle/gems/ruby/2.2.0/gems/rack-1.6.0/lib/rack/runtime.rb:18:in call' .bundle/gems/ruby/2.2.0/gems/activesupport-4.2.1/lib/active_support/cache/strategy/local_cache_middleware.rb:28:incall'
.bundle/gems/ruby/2.2.0/gems/rack-1.6.0/lib/rack/lock.rb:17:in call' .bundle/gems/ruby/2.2.0/gems/actionpack-4.2.1/lib/action_dispatch/middleware/static.rb:113:incall'
.bundle/gems/ruby/2.2.0/gems/rack-1.6.0/lib/rack/sendfile.rb:113:in call' .bundle/gems/ruby/2.2.0/gems/railties-4.2.1/lib/rails/engine.rb:518:incall'
.bundle/gems/ruby/2.2.0/gems/railties-4.2.1/lib/rails/application.rb:164:in call' .bundle/gems/ruby/2.2.0/gems/rack-1.6.0/lib/rack/content_length.rb:15:incall'
.bundle/gems/ruby/2.2.0/gems/puma-2.11.2/lib/puma/server.rb:507:in handle_request' .bundle/gems/ruby/2.2.0/gems/puma-2.11.2/lib/puma/server.rb:375:inprocess_client'
.bundle/gems/ruby/2.2.0/gems/puma-2.11.2/lib/puma/server.rb:262:in block in run' .bundle/gems/ruby/2.2.0/gems/puma-2.11.2/lib/puma/thread_pool.rb:104:incall'
.bundle/gems/ruby/2.2.0/gems/puma-2.11.2/lib/puma/thread_pool.rb:104:in `block in spawn_thread'

@campgurus

This comment has been minimized.

campgurus commented Aug 13, 2015

Same as #84.

@ankane

This comment has been minimized.

Owner

ankane commented Aug 14, 2015

Is properties a text column on ahoy_events?

@campgurus

This comment has been minimized.

campgurus commented Aug 14, 2015

It was initially, but I changed it to jsonb. from schema.rb

create_table "ahoy_events", id: :uuid, default: nil, force: :cascade do |t|
t.uuid "visit_id"
t.integer "user_id"
t.string "name"
t.jsonb "properties"
t.datetime "time"
end

add_index "ahoy_events", ["time"], name: "index_ahoy_events_on_time", using: :btree
add_index "ahoy_events", ["user_id"], name: "index_ahoy_events_on_user_id", using: :btree
add_index "ahoy_events", ["visit_id"], name: "index_ahoy_events_on_visit_id", using: :btree

@constantm

This comment has been minimized.

constantm commented Sep 3, 2015

I'm running into the same issue, and we're running Postgres 9.3. Changing column type to JSON results in no implicit conversion of Hash into String when trying to do anything with Ahoy::Event. Is there no other way to query events from properties without using Postgres 9.4/jsonb?

@mbajur

This comment has been minimized.

mbajur commented Sep 3, 2015

+1

@hothero

This comment has been minimized.

hothero commented Dec 20, 2015

+1, Anyone have any solution for this??

I got the same error after upgrading properties filed from json to jsonb type. I ensure my postgresql version is 9.4.4.

@Fudoshiki

This comment has been minimized.

Fudoshiki commented Jan 13, 2016

@abrambailey

This comment has been minimized.

abrambailey commented Apr 10, 2016

+1

@hothero

This comment has been minimized.

hothero commented Apr 10, 2016

This problem could be solved by issue#86 or you can follow my article: https://blog.hothero.org/2016/04/10/fix-ahoy-no-implicit-conversion-of-hash-into-string/

@genlighten

This comment has been minimized.

genlighten commented Jun 18, 2016

I'm trying to query events by profile_id (from my properties hash). I'm using MySQL rather than PostgresQL. My Ahoy::Events look like this:

#<Ahoy::Event id: 4, visit_id: 2, user_id: 2, name: "Viewed profile", properties: {"profile_id"=>2, "referral_url"=>"/genealogists/chicago-il"}, time: "2016-06-16 03:41:49">

When I try to query for events that have profile_id 2, using this syntax:

Ahoy::Event.where("properties LIKE '%\"profile_id\"=>2%'").count

in the console, the returned value is zero, even though there are four entries in my development database with profile_id=>2.

I'm guessing I'm just using the wrong syntax for the MySQL query. Any suggestions on what I need to change?

Thanks!

@ankane

This comment has been minimized.

Owner

ankane commented Jun 21, 2016

Hi Dean, see the Querying Properties section. For those who are getting the no implicit conversion of Hash into String error, see #86 for the solution.

@ankane ankane closed this Jun 21, 2016

@genlighten

This comment has been minimized.

genlighten commented Jun 21, 2016

Hi again, sorry to be a bother. I did follow the "text" syntax in the Querying Properties section, and it didn't work for my app that uses MySQL as the DB, as I explained in my post. It returned zero results, despite there being several in the database.

I tried setting the "name" field for my events to "Viewed profile #{profile_id}", then setting up an AR query like this:

first_event = Ahoy::Event.where(:name => "Viewed profile #{profile_id}").last and that worked fine, but I'd really rather query the profile_id from within the properties hash as you've designed it.

I'm guessing that it's a simple MySQL syntax error, but I've checked my use of %, escaping the quote marks, etc. and they seem right.

Thanks!

--Dean

@ankane

This comment has been minimized.

Owner

ankane commented Jun 21, 2016

It looks like you're using Ruby syntax (i.e. =>) instead of JSON syntax. Try the following to see what works.

Ahoy::Event.where("properties LIKE '%\"profile_id\": \"2\"%'").count
Ahoy::Event.where("properties LIKE '%\"profile_id\": 2%'").count
Ahoy::Event.where("properties LIKE '%\"profile_id\":\"2\"%'").count
Ahoy::Event.where("properties LIKE '%\"profile_id\":2%'").count
@genlighten

This comment has been minimized.

genlighten commented Jun 21, 2016

The last one worked perfectly. Thanks!

--Dean

On Mon, Jun 20, 2016 at 9:04 PM, Andrew Kane notifications@github.com
wrote:

It looks like you're using Ruby syntax (i.e. =>) instead of JSON syntax.
Try the following to see what works.

Ahoy::Event.where("properties LIKE '%"profile_id": "2"%'").countAhoy::Event.where("properties LIKE '%"profile_id": 2%'").countAhoy::Event.where("properties LIKE '%"profile_id":"2"%'").countAhoy::Event.where("properties LIKE '%"profile_id":2%'").count


You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#120 (comment), or mute
the thread
https://github.com/notifications/unsubscribe/AABbk95gj-8I5YoVgpM0E-RREtldbuZFks5qN2LVgaJpZM4Fq2P5
.

@ankane

This comment has been minimized.

Owner

ankane commented Jun 21, 2016

Great 👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment