Rails: How to sort models by an associated model’s field with condition?

I have three models:

class Unit < ApplicationRecord
  has_many :feedback_units
  has_many :feedbacks, through: :feedback_units
end

class Feedback < ApplicationRecord
  has_many :feedback_units
  has_many :units, through: :feedback_units
end

class FeedbackUnit < ApplicationRecord
  belongs_to :unit
  belongs_to :feedback
end

This is what I want:

Feedback model has a result field. If result is true or false, the feedback is closed. If result is nil, the feedback is unclosed. I want to sort from the unit with the highest number of unclosed feedbacks to the unit with the lowest. It should be at the bottom of the list even if the unit has no feedback.

For example:

Unit	Number of Closed Feedbacks	Number of Unclosed Feedbacks	Total Number of Feedbacks
Unit_A	14	5	19
Unit_B	35	23	58
Unit_C	112	17	129
Unit_D	0	0	0
Unit_E	36	0	36
Unit_F	0	19	19
Unit_G	0	0	0

It should be sorted as follows:

Unit	Number of Closed Feedbacks	Number of Unclosed Feedbacks	Total Number of Feedbacks
Unit_B	35	23 <-	58
Unit_F	0	19 <-	19
Unit_C	112	17 <-	129
Unit_A	14	5 <-	19
Unit_D	0	0 <-	0
Unit_E	36	0 <-	36
Unit_G	0	0 <-	0

It doesn’t matter how the units without unclosed feedbacks are sorted. The last three units can be sorted as follows:

Unit	Number of Closed Feedbacks	Number of Unclosed Feedbacks	Total Number of Feedbacks
.	.	.	.
Unit_E	36	0	36
Unit_D	0	0	0
Unit_G	0	0	0

I tried to do it this way but it didn’t:

Unit.distinct
    .left_joins(:feedbacks)
    .group('feedbacks.result IS NULL')
    .order('feedbacks.result IS NULL ASC, COUNT(feedbacks.result IS NULL)')

This should be in the single query and return ActiveRelation.

How can I do that?

Answer

Unit.left_joins(:feedbacks).group("units.id").select(
    'units.id,
     sum(case when feedbacks.result is null and feedbacks.id is not null then 1 else 0 end) AS unclosed, 
     sum(case when feedbacks.result is not null then 1 else 0 end) AS closed, 
     sum(case when feedbacks.id is not null then 1 else 0 end) as total').order('unclosed DESC')

This will work for both PostgreSQL and MySql