3

I am using the Ahoy gem to record user actions in a Rails 4 app. Events are being recorded and stored in the Ahoy::Event table. Each event has a properties hash attribute stored as text. In this case, am trying to return the number of article views for a specific article.

I am following the suggested syntax in the gem docs here: https://github.com/ankane/ahoy#querying-properties, which gives an example of Ahoy::Event.where("properties LIKE '%\"store_id\": \"1\"%'").count for querying a properties text hash, but I have been unable to return accurate results.

Here is the controller event I am tracking to test this:

def show @article = Article.friendly.find(params[:id]) ... ahoy.track "Viewed article", article_id: @article.id ... end

In console, as expected I can see one event is returned when I search for articles with the name: "Viewed Article":

Ahoy::Event.where(name: "Viewed article") Ahoy::Event Load (89.1ms) SELECT "ahoy_events".* FROM "ahoy_events" WHERE "ahoy_events"."name" = $1 [["name", "Viewed article"]] => #<ActiveRecord::Relation [#<Ahoy::Event id: "6cda3790-625d-4b73-8ef0-262a26b29618", visit_id: "2d56efa6-3590-4a64-acec-c0fd0ba07727", user_id: nil, name: "Viewed article", properties: {"article_id"=>562}, time: "2016-06-03 18:49:28">]>

But when I attempt to find the same event by querying for events with the corresponding article_id, the event is not found:

Ahoy::Event.where("properties LIKE '%\"article_id\": \"562\"%'").count (207.5ms) SELECT COUNT(*) FROM "ahoy_events" WHERE (properties LIKE '%"article_id": "562"%') => 0

Any suggestions are much appreciated.

user2799827
  • 1,077
  • 3
  • 18
  • 54

1 Answers1

1

Update: Ahoy actually has a wonderful where_properties method which does what you want, and takes your database type into account! It looks like this:

Ahoy::Event.where(name: "Viewed product").where_properties(product_id: 123).count

Documentation here

You are really fighting uphill by using a text column to store your properties hash. The hash must be encoded into text before it's stored in your database, so now you have to query your database based on the encoded form (which varies based on which encoding mechanism you use). Ugh! My suggestions:

  1. Best thing to do by far is to store the properties hash in a JSON column! Not all databases support this, but if you can, definitely do this instead. You can then make direct queries for JSON properties, and even make indexes, etc. This is how I do it, and it works very well. Yay Postgres!

  2. Don't try to query the properties hash until it's been decoded into a Ruby hash in memory. Make the most specific query you can with everything except the properties hash (like the user_id & event_name), then just loop through the resulting Ahoy::Event objects checking their properties in Ruby. It may be slow, but it will be simple and bug-free. For performance optimizations, you could always do it in a background job and cache the results!

Kyletns
  • 444
  • 4
  • 4