CODEWITHSUNDEEP
ruby-on-railsdatabasequerying
user2985898
user2985898

Reputation: 1203

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 are the two models in question.

class Person < ApplicationRecord
  has_many :awards
end

class Award < Application Record
  belongs_to :person
end

The Award model has a column called rank which can be 1, 2, or 3, and a column called category (also integers, but let's just use letters for now).

I'm trying to rank People based on their awards of a certain rank. Many people can have the same amount of a rank. For example, 40 people might have 30 rank 1 awards of category X.

For example:

Most Awards for Category A

People Granted the Award with Rank 1 8 Times

People Granted the Award with Rank 2 30 Times

People Granted the Award with Rank 3 60 Times

I want to build a query that returns a collection of People.

Logically, this is what my query should do (in pseudo code):

Get all `people` who have the maximum `count` of awards where `awards.category` is `A` and `awards.rank` is `1`.

...and repeat this for rank 2 and rank 3.

A counter_cache would not work because of the implication of categories (unless I create a counter_cache for each category, but that feels far redundant and not flexible).

As usual, an idea that sounded simple turned out to be far more complex than I thought, and I have no idea how to pull this off.

Upvotes: 1

Views: 77

Answers (1)

Jay-Ar Polidario
Jay-Ar Polidario

Reputation: 6603

awards_having_max_count_of_persons_for_each_category_and_rank = Award.group(:category, :rank).order('count(awards.person_id)')

# you can do something like this if you'd like to key-value pairs:
hash = {}
awards_having_max_count_of_persons_for_each_category_and_rank.each do |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,
#      ...
#    }
# where 1, 3, 2, and 9 are person_ids

Upvotes: 1

Related Questions