Group statuses together and sort within those status with 1 SQL call in Rails

Question

I have a table profiles that have three notable fields: price, rating and status. So far I've figured out how to sort by rating in descending order, then sort by price in ascending order among records with the same rating with:

Profile.all.order('rating DESC, price ASC')

And vice versa by sorting price ascending then rating descending among records with the same price:

Profile.all.order('price ASC, rating DESC')

However, I have a column status that is of integer type with enum [:pending, :verified, :rejected]. I would like to make 1 SQL call that first groups the records ofstatus: verifiedand every other status (rejectedandpending) after theverifiedgroup. Within theverified` group, it would sort all records by rating DESC then price ASC or vice versa. Within the "other" group, it would do the same thing.

The end result will be 2 sorted lists where ALL verified records appear before the "other" records.

I could do Profile.all.order('status DESC, rating DESC, price ASC'), and rearrange verified to be the largest index in the enum, but I would like rejected and pending to be mixed together.

Answer 1

I just think the query will be like:

Profile.joins("LEFT OUTER JOIN ( SELECT id, rating, price
                                 FROM profiles
                                 WHERE status = 'verified'
                               ) AS temp ON temp.id = profiles.id")
       .order("temp.rating DESC NULLS LAST, temp.price ASC NULLS LAST, profiles.rating DESC, profiles.price ASC")

Explanation:

• After left outer join, we order by temp.rating DESC NULLS LAST that means we order by rating of profile which has status = verified, for profiles status != verified, their temp.rating and temp.price will be NULL, that is why NULLS LAST was using here
• The same idea for temp.price

Btw, you may change WHERE status = 'verified' to work with your case, because maybe status = 1 for eg