In my schema, a user can vote for different monsters that have different powers (eg lighting, fire) and different bodies. Body is a polymorphic association, as it can be from different types of animals. Here's the relevant pieces of the schema:
votes:
monster_id
power_id
body_id #polymorphic association
body_type #polymorphic association
For every combination of power and body with representation on the votes table, I want to find out the monsters that got the most votes.
Eg of a specific example:
--------------------------------------------------
| votes |
--------------------------------------------------
| monster_id| power_id | body_id | body_type |
--------------------------------------------------
| 1 | 1 | 1 | Body::Mammal |
| 2 | 1 | 1 | Body::Mammal |
| 2 | 1 | 1 | Body::Mammal |
| 11 | 2 | 11 | Body::Reptile |
| 11 | 2 | 11 | Body::Reptile |
| 22 | 2 | 11 | Body::Reptile |
--------------------------------------------------
Results I would like:
- ["For the combination (power_id: 1, body_id: 1, body_type: Body::Mammal), the monster with most votes is monster_id: 2",
"For the combination (power_id: 2, body_id: 11, body_type: Body::Reptile), the monster with most votes is monster_id: 11",
...]
I am using Rails 6 and postgres so I have the option to use ActiveRecord, for which I have a slight preference, but I realize this likely needs raw sql.
I understand the answer is very likely an extension of the one given in this question, where they do a similar thing with less attributes, but I can't seem to add the extra complexity needed to accommodate increased number of columns in play. sql: select most voted items from each user
If I follow you correctly, you can use distinct on and aggregation:
select distinct on (body_id, power_id, body_type)
body_id, power_id, body_type, monster_id, count(*) cnt_votes
from votes
group by body_id, power_id, body_type, monster_id
order by body_id, power_id, body_type, count(*) desc