Get all records grouped by field from association and sorted by count in group

Question

I have 3 models: Post, Comment, User

Post has many Comments

Comment belongs to User

User has field country_code

I want to get all post comments grouped by country code AND sorted by amount of comments per country.

This query:

post.comments.joins(:user).group("users.country_code").order('count_all desc').count

returns such kind of result:

{"DE"=>67,
"US"=>8,
"RS"=>8,
"IN"=>8,
"ES"=>7,
"BR"=>6,
...
"UA"=>0

}

What I need is a similar result where country codes are keys but values are arrays of comments. I don't know how to achieve this.

Solution 1

You could use the group_by that comes with the Ruby enumeration module

post.comments.group_by{ |c| c.user.country_code }

If you also want it ordered by amount of comments in each group that's also possible:

post.comments.group_by{ |c| c.user.country_code }.sort_by{ |k, v| v.length }

I suppose to get the sorting in the opposite direction you could multiply the length by -1 in the sort block.

post.comments.group_by{ |c| c.user.country_code }.sort_by{ |k, v| v.length * -1 }

Solution 2

Try something like this: (Un-tested):

post.comments.joins(:users).select("users.country_code, count(1) as count_all").group("users.country_code").order('count_all desc')

Comments

[Toby 1 Kenobi]

If you use the ActiveRecord method group it will aggregate the result as you say, but if you use the completely different Ruby method group_by as shown in my answer then it doesn't aggregate, and gives the desired result. Don't use a complicated inject block to do the same thing that group_by does simply.

[tommyalvarez]

The performance of this could be terrible if the list of comments is large (imaginate a viral facebook post with comments), because group_by loads all the comments in memory from the database. This should be avoided and only be used with fixed known size collections or at least variable with known max size