1

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

  • Person 1
  • Person 2

People Granted the Award with Rank 2 30 Times

  • Person 1
  • Person 7
  • Person 19

People Granted the Award with Rank 3 60 Times

  • Person 2
  • Person 19
  • Person 44

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.

user2985898
  • 1,173
  • 1
  • 8
  • 21
  • Is it more like *Get all `people` in descending order of `count` of awards where `awards.category` is `A` and `awards.rank` is `1`*? – Jay-Ar Polidario Jul 25 '17 at 12:22
  • The order shouldn't matter because I want all of the `people` who have the maximum `count` of a certain award of a category and rank. I can do something like this: `Award.where(rank: 1, category: 0).group(:person_id).count` Which will return an ordered hash of `{ id(integer) => count(integer) }`, and then sort by the value, and get all of the ids (the key of the hash) that have the highest count (there can be multiple people ranked 1st). – user2985898 Jul 25 '17 at 12:34
  • However, the solution I commented above is definitely not elegant, and would probably be slow, especially if I am doing this with various combinations of categories and ranks on one page. – user2985898 Jul 25 '17 at 12:52

1 Answers1

1
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
Jay-Ar Polidario
  • 6,463
  • 14
  • 28
  • having `COUNT(awards.id)` will always return 1 as a count... I guess you mean `COUNT(awardes.user_id)` – davegson Jul 25 '17 at 12:38
  • 1
    oh you're right! :) Although I think I need to revise my answer as the OP just replied to my comment above, and looks like this is not exactly what he/she wants. – Jay-Ar Polidario Jul 25 '17 at 12:39
  • oh wait. no actually it should be `awards.id`, as Im sorting the count of the associated awards records. – Jay-Ar Polidario Jul 25 '17 at 12:41
  • Although, this was before I thought I understood the OP, but I still need to update my answer, to reflect just like what you have described. – Jay-Ar Polidario Jul 25 '17 at 12:42
  • I tried it regardless, but I got this error message: `ActiveRecord::StatementInvalid: SQLite3::SQLException: misuse of aggregate: COUNT(): SELECT "people".* FROM "people" LEFT OUTER JOIN "awards" ON "awards"."person_id" = "people"."id" WHERE "awards"."category" = ? AND "awards"."rank" = ? ORDER BY COUNT(awards.person_id) DESC LIMIT ?` – user2985898 Jul 25 '17 at 12:48
  • I added `group(:id)` after a while. Can you try with that? – Jay-Ar Polidario Jul 25 '17 at 12:49
  • @user2985898 I just updated my answer. Hope this works. – Jay-Ar Polidario Jul 25 '17 at 13:09
  • ... I feel I asked the question prematurely because it turns out what I was looking for wasn't the best solution. Instead, I'm looking for a way to get the top people with the best scores, similar to the olympics: For example, a person who has `[7, 12, 32]` would be higher ranked than a person who has `[1, 32, 48]` (where index 0 is amount of gold medals, index 1 silver and index 2 bronze). Can't be `limit(1)` since 2 people might have an identical score. So I guess this question will lead to a dead-end (though your answers did help me understand some concepts, so I thank you for that). – user2985898 Jul 26 '17 at 04:53
  • Since the fundamental question has changed, rather than editing this question, I asked a new question here: https://stackoverflow.com/questions/45318204/rails-order-by-count-based-on-values-of-column – user2985898 Jul 26 '17 at 05:33
  • No worries. I'll see if I can answer your question there. – Jay-Ar Polidario Jul 26 '17 at 08:26