database - Rails: How to get all of the records that have the maximum count of a relation with a where clause -

rails 5.1.2

these 2 models in question.

class person < applicationrecord   has_many :awards end  class award < application record   belongs_to :person end 

the award model has column called rank can 1, 2, or 3, , column called category (also integers, let's use letters now).

i'm trying rank people based on awards of rank. many people can have same amount of rank. example, 40 people might have 30 rank 1 awards of category x.

for example:

most awards category a

people granted award rank 1 8 times

• person 1
• person 2

people granted award rank 2 30 times

• person 1
• person 7
• person 19

people granted award rank 3 60 times

• person 2
• person 19
• person 44

i want build query returns collection of people.

logically, query should (in pseudo code):

get `people` have maximum `count` of awards `awards.category` `a` , `awards.rank` `1`. 

...and repeat rank 2 , rank 3.

a counter_cache not work because of implication of categories (unless create counter_cache each category, feels far redundant , not flexible).

as usual, idea sounded simple turned out far more complex thought, , have no idea how pull off.

awards_having_max_count_of_persons_for_each_category_and_rank = award.group(:category, :rank).order('count(awards.person_id)')  # can if you'd key-value pairs: hash = {} awards_having_max_count_of_persons_for_each_category_and_rank.each |award|   hash[{category: award.category, rank: award.rank}] = award.person_id end  puts hash # => { #      {category: 1, rank: 1} => 1, #      {category: 1, rank: 2} => 3, #      {category: 1, rank: 3} => 2, #      {category: 2, rank: 1} => 9, #      ... #    } # 1, 3, 2, , 9 person_ids