Simulating MySQL's ORDER BY FIELD() in Postgresql
MysqlPostgresqlMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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;
Solution 2 - Mysql
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]
Solution 3 - Mysql
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)
Solution 4 - Mysql
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.
Solution 5 - Mysql
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 fetch 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.
Solution 6 - Mysql
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.
Solution 7 - Mysql
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
Solution 8 - Mysql
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
Solution 9 - Mysql
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.
Solution 10 - Mysql
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!
Solution 11 - Mysql
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.
Solution 12 - Mysql
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.
Solution 13 - Mysql
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;