Activerecord group count doesn't work

Question

I'm trying to write a query and I cannot find the right result. I have a model like this:

#<Ahoy::Event id: "007d74fb-6225-4cd5-a725-6e35b2e9a859", visit_id: "34713617-d6f6-4adb-b436-8029a7ea586c", user_id: 3, name: "view", properties: {"id"=>14, "delay"=>500, "action"=>"show", "resource"=>"News", "end_point"=>"news"}, time: "2016-02-12 15:13:05">

It's a tracker, so I have multiple resources and the same resource is tracked a lot of time if I return on the same page.

I want to know how many news the user read, so I try with:

Ahoy::Event.where(user_id:3).group(:properties["resource:news"]).count

Update

I'm also trying with

Ahoy::Event.where(user_id:3).where("properties @> (:key => :value)", :key => 'resource', :value => 'Gate').count

** Update 2 ** Another tentative:

Ahoy::Event.where(user_id:3).where("properties --> 'resource': 'Gate'").select("properties #>> 'id' as resource, count('resource')").group("resource")

JSONB has a specific syntax for querying. You will have to use that. I can't recall exactly what it is, but stackoverflow.com/questions/30519458/… you may have some hint. I'm not sure if you can explicitly GROUP BY a jsonb field value. — Simone Carletti
– Simone Carletti, Commented Feb 15, 2016 at 9:42

devanand · Accepted Answer · 2016-02-15 10:23:56Z

0

I've tried on my console in psql, it worked.

Maybe you are missing the curly brackets?

Ahoy::Event.where(user_id:3).
  where("properties @> ({:key => :value})", :key => 'resource', :value => 'Gate').
  count

answered Feb 15, 2016 at 10:23

devanand

5,4092 gold badges22 silver badges19 bronze badges

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

1 Comment

Roberto Pezzali Over a year ago

I notice that I do a mistake. I must group also by properties[:id] and I miss that part. I'm getting crazy with this query... I don't know how to group by properties id.

Collectives™ on Stack Overflow

Activerecord group count doesn't work

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related