Simulating MySQL's ORDER BY FIELD() in Postgre

Question

Just trying out PostgreSQL for the first time, coming from MySQL. In our Rails application we have a couple of locations with SQL like so:

SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC, name ASC

It didn't take long to discover that this is not supported/allowed in PostgreSQL.

Does anyone know how to simulate this behaviour in PostgreSQL or do we have to pull sorting out into the code?

Answer 1

Create a migration with this function

CREATE OR REPLACE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS bigint AS $$
  SELECT n FROM (
    SELECT row_number() OVER () AS n, x FROM unnest($2) x)
      numbered WHERE numbered.x = $1;
$$ LANGUAGE SQL IMMUTABLE STRICT;

Then just do this

sequence = [2,4,1,5]
Model.order("field(id,#{sequence.join(',')})")

voila!

Answer 2

If you'll run this often, add a new column and a pre-insert/update trigger. Then you set the value in the new column based on this trigger and order by this field. You can even add an index on this field.

Answer 3

Actually the version for postgres 8.1 as another advantage.

When calling a postgres function you cannot pass more than 100 parameters to it, so your ordering can be done at maximum on 99 elements.

Using the function using an array as second argument instead of having a variadic argument just remove this limit.

Answer 4

As I answered here, I just released a gem (order_as_specified) that allows you to do native SQL ordering like this:

CurrencyCode.order_as_specified(code: ['GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD'])

It returns an ActiveRecord relation, and thus can be chained with other methods, and it's worked with every RDBMS I've tested.

Answer 5

You can do this...

SELECT 
   ..., code
FROM 
   tablename
ORDER BY 
   CASE 
      WHEN code='GBP' THEN 1
      WHEN code='EUR' THEN 2
      WHEN code='BBD' THEN 3
      ELSE 4
   END

But why are you hardcoding these into the query -- wouldn't a supporting table be more appropriate?

--

Edit: flipped it around as per comments

Answer 6

sort in mysql:

> ids = [11,31,29]
=> [11, 31, 29]
> User.where(id: ids).order("field(id, #{ids.join(',')})")

in postgres:

def self.order_by_ids(ids)
  order_by = ["CASE"]
  ids.each_with_index do |id, index|
    order_by << "WHEN id='#{id}' THEN #{index}"
  end
  order_by << "END"
  order(order_by.join(" "))
end

User.where(id: [3,2,1]).order_by_ids([3,2,1]).map(&:id) 
#=> [3,2,1]