Rails: List items by frequency, including the frequency

Question

The following will get one post per category:

Post.group(:category_id).order('count *')

I could then show a table of representative posts, one per category by listing each result:

CATEGORY | SAMPLE POST ID | SAMPLE POST TITLE
        1        |             123           | How to swim out loud
        2        |             246           | On Hamlet

However, I'd like to include the count of posts in this category, e.g.:

CATEGORY | AMOUNT | SAMPLE POST ID | SAMPLE POST TITLE
        1        |       5       |            123           | How to swim out loud
        2        |       2       |            246           | On Hamlet

How can we also show the amount, ie the "count per group"? There's hopefully a way to extract it from the "Post" results as part of an appropriate ActiveRecord query.

Note there's no "Category" table here. category_id is just an independent column. Therefore counter_cache is not a possibility.

score 1 · Accepted Answer · answered Jan 10 '19 at 15:03

1

You can use some sql-magic (the following works with mysql, haven't tested in postgres, but should too):

posts = Post.group(:category_id).order('count(*)').select('count(*) as category_cnt, posts.*')
category_count = posts.first.category_cnt

answered Jan 10 '19 at 15:03

Vasfed

18,013
10
47
53

That's it! Very cool that AR supports a dynamic attribute based on the query, thanks – mahemoff Jan 10 '19 at 15:09

score 0 · Answer 2 · answered Jan 10 '19 at 11:31

0

I assume your Post belongs_to the Category.

That being said, there's an amazing feature in rails called counter_cache. Check the documentation here.

class Post < ApplicationRecord
  belongs_to :category, dependent: :destroy, counter_cache: true
end

class Category < ApplicationRecord
  has_many :posts
  # category should have a column called posts_count
end

Now, whenever you create a post, the category's posts_count will increment; when you delete it, it will decrement.

As for your listing, you can do something like:

Category.order(posts_count: :desc) and the get category.posts.first

answered Jan 10 '19 at 11:31

Roc Khalil

1,365
6
22

Ever had look on `counter_cache: true` in rails ? – ray Jan 10 '19 at 11:33
Actually, no, there's no other table here. I'm just looking for a generic way to do this kind of grouped count query, without counter_cache magic (which I agree is cool when it's possible). – mahemoff Jan 10 '19 at 11:35
@mahemoff so you have a `category_id` which is not a relationship to `category` table ? – Roc Khalil Jan 10 '19 at 12:41
Yes, it could be any enum type like "severity level". Even if there was another table, I'd be curious how to do it without memoizing the count, ie directly with a query. – mahemoff Jan 10 '19 at 12:47
@mahemoff not sure if this can help you: https://stackoverflow.com/a/36765671/2903313 but it's more like querying and sub-querying tables; and you can then read the result as a hash – Roc Khalil Jan 10 '19 at 13:01

Rails: List items by frequency, including the frequency

2 Answers2