Simulating MySQL's ORDER BY FIELD() in Postgresql

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

Ah, gahooa was so close:

SELECT * FROM currency_codes
  ORDER BY
  CASE
    WHEN code='USD' THEN 1
    WHEN code='CAD' THEN 2
    WHEN code='AUD' THEN 3
    WHEN code='BBD' THEN 4
    WHEN code='EUR' THEN 5
    WHEN code='GBP' THEN 6
    ELSE 7
  END,name;

Answer 2

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]

Answer 3

Update, fleshing out terrific suggestion by @Tometzky.

This ought to give you a MySQL FIELD()-alike function under pg 8.4:

-- SELECT FIELD(varnames, 'foo', 'bar', 'baz')
CREATE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS numeric AS $$
  SELECT
    COALESCE(
     ( SELECT i FROM generate_subscripts($2, 1) gs(i)
       WHERE $2[i] = $1 ),
     0);
$$ LANGUAGE SQL STABLE

Mea culpa, but I cannot verify the above on 8.4 right now; however, I can work backwards to a "morally" equivalent version that works on the 8.1 instance in front of me:

-- SELECT FIELD(varname, ARRAY['foo', 'bar', 'baz'])
CREATE OR REPLACE FUNCTION field(anyelement, anyarray) RETURNS numeric AS $$
  SELECT
    COALESCE((SELECT i
              FROM generate_series(1, array_upper($2, 1)) gs(i)
              WHERE $2[i] = $1),
             0);
$$ LANGUAGE SQL STABLE

More awkwardly, you still can portably use a (possibly derived) table of currency code rankings, like so:

pg=> select cc.* from currency_codes cc
     left join
       (select 'GBP' as code, 0 as rank union all
        select 'EUR', 1 union all
        select 'BBD', 2 union all
        select 'AUD', 3 union all
        select 'CAD', 4 union all
        select 'USD', 5) cc_weights
     on cc.code = cc_weights.code
     order by rank desc, name asc;
 code |           name
------+---------------------------
 USD  | USA bits
 CAD  | Canadian maple tokens
 AUD  | Australian diwallarangoos
 BBD  | Barbadian tridents
 EUR  | Euro chits
 GBP  | British haypennies
(6 rows)

Answer 4

This is I think the simplest way:

create temporary table test (id serial, field text);
insert into test(field) values
  ('GBP'), ('EUR'), ('BBD'), ('AUD'), ('CAD'), ('USD'),
  ('GBP'), ('EUR'), ('BBD'), ('AUD'), ('CAD'), ('USD');
select * from test
order by field!='GBP', field!='EUR', field!='BBD',
  field!='AUD', field!='CAD', field!='USD';
 id | field 
----+-------
  1 | GBP
  7 | GBP
  2 | EUR
  8 | EUR
  3 | BBD
  9 | BBD
  4 | AUD
 10 | AUD
  5 | CAD
 11 | CAD
  6 | USD
 12 | USD
(12 rows)

In PostgreSQL 8.4 you can also use a function with variable number of arguments (variadic function) to port field function.

Answer 5

SELECT * FROM (VALUES ('foo'), ('bar'), ('baz'), ('egg'), ('lol')) t1(name)
ORDER BY ARRAY_POSITION(ARRAY['foo', 'baz', 'egg', 'bar'], name)

How about this? Above one fetches as below:

foo
baz
egg
bar
lol

As you already get it, if an element isn't in the array then it goes to the back. Note that you can also use the same technique if you need an IN operator with a custom sort order:

SELECT * FROM table
WHERE id IN (17, 5, 11)
ORDER BY ARRAY_POSITION(ARRAY[17, 5, 11], id);

Answer 6

ilgam's answer won't work since Rails 6.1, an ActiveRecord::UnknownAttributeReference error will be raised: https://api.rubyonrails.org/classes/ActiveRecord/UnknownAttributeReference.html

The recommended way is to use Arel instead of raw SQL.

In addition to ilgam's answer, here is the solution for Rails 6.1:

def self.order_by_ids(ids)
  t = User.arel_table
  condition = Arel::Nodes::Case.new(t[:id])
  ids.each_with_index do |id, index|
    condition.when(id).then(index)
  end
  order(condition)
end

Answer 7

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 8

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 9

Just define the FIELD function and use it. It's easy enough to implement. The following should work in 8.4, as it has unnest and window functions like row_number:

CREATE OR REPLACE FUNCTION field(text, VARIADIC text[]) 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;

You can also define another copy with the signature:

CREATE OR REPLACE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS bigint AS $$

and the same body if you want to support field() for any data type.

Answer 10

For folks coming here later, Rails 7 added in_order_of, so you can do:

CurrencyCode.in_order_of(:code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD')

See also: https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-in_order_of

Answer 11

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 12

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 13

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 14

It's also possible to do this ordering using the array unnest together WITH ORDINALITY functionality:

--- Table and data setup ...
CREATE TABLE currency_codes (
     code text null,
     name text
);
INSERT INTO currency_codes
  (code)
VALUES
  ('USD'), ('BBD'), ('GBP'), ('EUR'), ('AUD'), ('CAD'), ('AUD'), ('AUD');

-- ...and the Query
SELECT
  c.*
FROM
  currency_codes c
JOIN
  unnest('{"GBP", "EUR", "BBD", "AUD", "CAD", "USD"}'::text[])
  WITH ORDINALITY t(code, ord)
  USING (code)
ORDER BY t.ord DESC, c.name ASC;