rails sort by specifc order

Question

I have model named Group which has users_count field in it.

I have to order groups based upon dynamic preference of users_count like [3,4,2] means show groups with 3 users_count first, than with 4 and than with 2

Right now, I am using 3 separate queries and than merge records like

groups = Group.where(users_count: 3)
         + Group.where(users_count: 4)
         + Group.where(users_count: 2)

It works but It don't make sense to use 3 separate queries.

How can I achieve this in single query?

Does this help: https://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql — Ben Stephens, May 21 '21 at 06:57
Did you tried? `Group.where(user_count: [3,4,2]).group(:user_count)` — Aparichith, May 21 '21 at 08:22

score 3 · Accepted Answer · answered May 21 '21 at 08:47

Since 3, 4, 2 is not a sequential order there should be a custom condition to order them properly you can do it by using the CASE WHEN expression.

order_sql = Arel.sql(
  'CASE WHEN users_count = 3 THEN 0 ' \
    'WHEN users_count = 4 THEN 1 ' \
    'ELSE 3 END'
)
Group.where(users_count: [2,3,4]).order(order_sql)

Which will give 0 when users_count = 3, 1 when users_count = 4, and 3 for other cases. With default ascending order you'll get the result you want.

score -2 · Answer 2 · answered May 21 '21 at 07:51

-2

You can do

groups = Group.where(users_count: [3,4,2])

This will return the same groups as your 3 queries in a single query

answered May 21 '21 at 07:51

bashford7

223
3
9

1

The results will not neccessarily be in the same order as the values in your where clause. – CaTs May 21 '21 at 08:02
its not about getting records but order is required here. – Muhammad Faisal Iqbal May 21 '21 at 08:10

rails sort by specifc order

2 Answers2

Linked