Rails order by quantity based on column values
Rails 5.1.2
I have two models Student
and Award
as follows:
class Student < ApplicationRecord
has_many :awards
end
class Award < Application Record
belongs_to :students
# Categories
scope :attendance, -> { where(category: 0) }
#...(other award categories)
scope :talent, -> { where(category: 8) }
# Ranks
# Gold
scope :rank_1, -> { where(rank: 1) }
# Silver
scope :rank_2, -> { where(rank: 2) }
# Bronze
scope :rank_3, -> { where(rank: 3) }
end
Award
has the following columns: rank
and category
.
Now I want to get the top student for this category. The criteria for this are: order according to the count of "gold" awards (rank 1
), then order according to the column "silver" (rank 2
), and then order according to the count of "bronze" (rank 3
).
So, if I got Student
one that matches the top criteria for a category 0
(which is handled by the scope attendance
as described in the above model), this is what I thought the request should look like:
Student.joins(:awards).where(awards: { category: 0 }).group('students.id').order('COUNT(awards.rank == 1) DESC', 'COUNT(awards.rank == 2) DESC', 'COUNT(awards.rank == 3) DESC').take
However, this returns the student with the highest number of rewards, regardless of rank. For example, if I remove take
, the order looks like this:
# |St.ID | Gold | Slvr. | Brnz. |
----------------------------------
1 | 12 | 4 | 12 | 8 |
----------------------------------
2 | 1 | 9 | 0 | 4 |
----------------------------------
3 | 6 | 9 | 1 | 0 |
----------------------------------
4 | 18 | 5 | 2 | 2 |
----------------------------------
...
So the order I get is IDs 12, 1, 6, 18, ...
, when it should be IDs 6, 1, 18, 12, ...
.
I understand the part is order('COUNT(awards.rank == 1) DESC', 'COUNT(awards.rank == 2) DESC', 'COUNT(awards.rank == 3) DESC')
just ordering the count of the number of awards (not the number of awards with a specific value in the column rank
).
I can easily solve this by adding a cache counter for each award category, but this is not an elegant and flexible solution.
As a bonus, after this query returns a successful result, I will search the database again to find all students with the same score (how can there be connections). I don't know how to do it all in one query (maybe using subqueries after getting the values for each rank).
source to share
I think your problem might be double equals?
EDIT: This is the more correct way (assume MySQL):
Student.joins(:awards).where(awards: { category: 0 }).group('students.id').order('COUNT(if(awards.rank = 1)) DESC', 'COUNT(if(awards.rank = 2)) DESC', 'COUNT(if(awards.rank = 3)) DESC').take
Try your code without it first, if that doesn't work you can try this:
SELECT students.id, gold, silver, bronze) FROM students
JOIN ON
(SELECT students.id as id COUNT(awards) as bronze
FROM students JOIN awards ON students.id = awards.student_id
WHERE awards.rank = 1
GROUP BY students.id) q1 q1.id = students.id
JOIN ON
(SELECT students.id as id COUNT(awards) as silver
FROM students JOIN awards ON students.id = awards.student_id
WHERE awards.rank = 2
GROUP BY students.id) q2 q2.id = students.id
JOIN ON
(SELECT students.id as id COUNT(awards) as gold
FROM students JOIN awards ON students.id = awards.student_id
WHERE awards.rank = 3
GROUP BY students.id) q3 q3.id = students.id
ORDER BY gold, silver, bronze
source to share