Postgres — Order by elements index in array, similar to MySQL’s order by field()

Background:
So earlier today, I was struggling with a minor problem with ordering of elements of a select query based on the ids passed to it.
We had search results by elastic search API, which provided us element_ids in an array, ordered by the respective ranks, but when the same was passed to a select statement for postgres, the result set was all jumbled up. kinda like below:

select 
  * 
from 
  elements 
where 
  id in (6, 3, 2, 1);
=> id               content
   3                  x
   1                  y
   6                  a
   2                  b

There is a handy helper in MySQL for this same issue, Field()
Something like below would have worked with MySQL

SELECT 
  * 
FROM 
  elements 
where 
  id in (6, 3, 2, 1) 
ORDER BY 
  FIELD(id, 6, 3, 2, 1);

But with Postgres, we need to put some hack together to get our way.

Get Abhishek Verma’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

I build a helper function, something like below (with Ruby):

def order_by_rank(table_name, ids) 
  order_by = ['ORDER BY CASE'] 
  ids.each_with_index do |id, index| 
    order_by << "WHEN #{table_name}.id='#{id}' THEN #{index}" 
  end 
  order_by << 'END' 
  order_by.join(' ') 
end

And hooked it in the raw sql, something like:

select 
  * 
from 
  elements 
where 
  id in (6, 3, 2, 1) #{order_by_rank(elements, [6,3,2,1])};

And voila! the results were in proper order now!

There is another workaround for the same issue, something like below:

select 
  elements.* 
from 
  elements 
  join (
    values 
      (6, 1), 
      (3, 2), 
      (2, 3), 
      (1, 4)
  ) as x (id, ordering) on elements.id = x.id 
where 
  elements.id IN (6, 3, 2, 1) 
order by 
  x.ordering

Originally published at https://adevelopersdiary.com