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.

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}"
    order_by << 'END'

    order_by.join(' ')

And hooked it in the raw sql, something like:

  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 (
) as x (id, ordering) on =
where IN (6,3,2,1)
order by x.ordering