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?
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.
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
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;
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