第一次尝试使用PostgreSQL,之前一直使用MySQL。在我们的Rails应用程序中,有几个位置使用类似如下的SQL语句:
SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC, name ASC
很快就发现PostgreSQL不支持/允许这个。有人知道如何在PostgreSQL中模拟这种行为,还是必须将排序拉出来放到代码中?
第一次尝试使用PostgreSQL,之前一直使用MySQL。在我们的Rails应用程序中,有几个位置使用类似如下的SQL语句:
SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC, name ASC
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;
order by id=e, id=k, id=a, id=j
。 - jakeonrailsMySQL中的排序:
> ids = [11,31,29]
=> [11, 31, 29]
> User.where(id: ids).order("field(id, #{ids.join(',')})")
在PostgreSQL中:
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]
order_by_ids
在 Rails 6.1 上无法使用,它会引发 ActiveRecord::UnknownAttributeReference
错误 https://api.rubyonrails.org/classes/ActiveRecord/UnknownAttributeReference.html - gaotongfei更新:根据@Tometzky的绝妙建议进行完善。
这应该可以在pg 8.4下提供类似于MySQL FIELD()
的功能:
-- 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
我的过失,但我现在无法验证上面在8.4上的内容;不过,我可以倒推到一个在我面前的8.1实例上“道德”上等效的版本:
-- 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
更尴尬的是,您仍然可以便携地使用一个(可能派生的)货币代码排名表,如下所示:
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)
我认为这是最简单的方法:
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)
field
函数。order-by-bang
的建议加一,对于 VARIADIC
的建议,我会尝试实现。 - pilcrowSELECT * FROM (VALUES ('foo'), ('bar'), ('baz'), ('egg'), ('lol')) t1(name)
ORDER BY ARRAY_POSITION(ARRAY['foo', 'baz', 'egg', 'bar'], name)
这样怎么样?上面的代码获取结果如下:
foo
baz
egg
bar
lol
如你已经知道的那样,如果一个元素不在数组中,它就会被移到数组末尾。请注意,如果您需要使用自定义排序顺序的IN
操作符,也可以使用相同的技巧:
SELECT * FROM table
WHERE id IN (17, 5, 11)
ORDER BY ARRAY_POSITION(ARRAY[17, 5, 11], id);
ilgam的回答在Rails 6.1中已经不可行,会引发ActiveRecord::UnknownAttributeReference
错误: https://api.rubyonrails.org/classes/ActiveRecord/UnknownAttributeReference.html
推荐的方法是使用Arel代替原始SQL。
除了ilgam的解答,以下是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
实际上,Postgres 8.1 版本有另一个优点。
调用 Postgres 函数时,您不能传递超过 100 个参数,因此您的排序最多可以在 99 个元素上进行。
使用数组作为第二个参数来使用函数,而不是使用可变参数,可以消除此限制。
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
对于后来者,Rails 7 添加了 in_order_of
,因此您可以执行以下操作:
CurrencyCode.in_order_of(:code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD')
query.reorder(nil).in_order_of(:current_status, ordered_statuses).order(starts_on: :asc)
- Topher Fangioquery.reorder(nil).in_order_of(:current_status, ordered_statuses).order(starts_on: :asc)
- undefined只需要定义FIELD
函数并使用它即可。实现起来非常简单。以下代码可以在8.4中使用,因为它有unnest
和窗口函数,如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;
CREATE OR REPLACE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS bigint AS $$
如果您想要支持任何数据类型的field()
,需要相同的代码体。
Select version()
- Craig Ringerunnest
从8.4版本开始就存在了...请显示\df unnest
- Craig Ringer
#in_order_of
https://github.com/rails/rails/pull/42061 - João Souza