CODEWITHSUNDEEP
ruby-on-railsrubyrails-activerecord
Shkarik
Shkarik

Reputation: 1159

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

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.

Upvotes: 6

Views: 13615

Answers (3)

dinomix
dinomix

Reputation: 976

I think if you use group by the grouping will occur in sql returning an aggregate result which won't have all of the comments. You should include user and then group in ruby. Something like this:

post.comments.includes(:users).inject({}){|r, x| r[x.user.country_code].nil? ? r[x.user.country_code] = [x] : r[x.user.country_code] << x;r}

Upvotes: -2

K M Rakibul Islam
K M Rakibul Islam

Reputation: 34338

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')

Upvotes: 4

Toby 1 Kenobi
Toby 1 Kenobi

Reputation: 5037

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 }

Upvotes: 8

Related Questions