我正试图从自动引用表上的查询中检索分层排序的结果，如下所示：
create table category (
id serial,
-- parent category,
parent_id integer default null, -- null for root category
-- tree control
tree_depth smallint not null, -- calculated
primary key (id),
unique (parent_id, id),
foreign key (parent_id) references category (id)
);
除了需要支持多种语言之外，这是存储类别树的常用方法。为此，我们加入了这样一个依赖于语言的表：
create table category_lang (
id serial,
-- natural primary key
category_id integer not null,
lang_code char(2) not null,
-- language-dependent data
title varchar(128) not null,
primary key (id),
unique (category_id, lang_code)
);
tree_depth列在before insert触发器中计算，如下所示：
create or replace function fn_category__bins () returns trigger as $$
begin
-- calculate tree_depth as parent tree_depth + 1
if new.parent_id is null then
new.tree_depth = 0;
else
new.tree_depth = (select tree_depth from category where id = new.parent_id limit 1) + 1;
end if;
return new;
end;
$$ language plpgsql;
create trigger tg_category__bins before insert on category for each row
execute procedure fn_category__bins();
我们用两种语言的易读文本填充表格：
insert into category (parent_id, id) values
(null, 1),
(null, 2),
(null, 3),
(1, 11),
(1, 12),
(1, 13),
(2, 21),
(2, 22),
(3, 31),
(21, 211),
(21, 212),
(21, 213);
-- lang_code = 'EN'
insert into category_lang (category_id, title, lang_code) values
(1, 'One', 'EN'),
(2, 'Two', 'EN'),
(3, 'Three', 'EN'),
(11, 'One.One', 'EN'),
(12, 'One.Two', 'EN'),
(13, 'One.Three', 'EN'),
(21, 'Two.One', 'EN'),
(22, 'Two.Two', 'EN'),
(31, 'Three.One', 'EN'),
(211, 'Two.One.One', 'EN'),
(212, 'Two.One.Two', 'EN'),
(213, 'Two.One.Three', 'EN');
-- lang_code = 'ES'
insert into category_lang (category_id, title, lang_code) values
(1, 'Uno', 'ES'),
(2, 'Dos', 'ES'),
(3, 'Tres', 'ES'),
(11, 'Uno.Uno', 'ES'),
(12, 'Uno.Dos', 'ES'),
(13, 'Uno.Tres', 'ES'),
(21, 'Dos.Uno', 'ES'),
(22, 'Dos.Dos', 'ES'),
(31, 'Tres.Uno', 'ES'),
(211, 'Dos.Uno.Uno', 'ES'),
(212, 'Dos.Uno.Dos', 'ES'),
(213, 'Dos.Uno.Tres', 'ES');
一个简单的查询会产生这样的自然结果：
select * from category tc
left outer join category_lang tl on tl.category_id = tc.id and tl.lang_code = 'EN';
id |parent_id|tree_depth|id|category_id|lang_code|title |
---|---------|----------|--|-----------|---------|-------------|
1| | 0| 1| 1|EN |One |
2| | 0| 2| 2|EN |Two |
3| | 0| 3| 3|EN |Three |
11| 1| 1| 4| 11|EN |One.One |
12| 1| 1| 5| 12|EN |One.Two |
13| 1| 1| 6| 13|EN |One.Three |
21| 2| 1| 7| 21|EN |Two.One |
22| 2| 1| 8| 22|EN |Two.Two |
31| 3| 1| 9| 31|EN |Three.One |
211| 21| 2|10| 211|EN |Two.One.One |
212| 21| 2|11| 212|EN |Two.One.Two |
213| 21| 2|12| 213|EN |Two.One.Three|
当期望的顺序应该符合英文的树层次结构和字母顺序时(在每个深度级别(，如下所示：
[编辑以修复Erwin识别的错误]
id |parent_id|tree_depth|id|category_id|lang_code|title |
---|---------|----------|--|-----------|---------|-------------|
1| | 0| 1| 1|EN |One |
11| 1| 1| 4| 11|EN |One.One |
13| 1| 1| 6| 13|EN |One.Three |
12| 1| 1| 5| 12|EN |One.Two |
3| | 0| 3| 3|EN |Three |
31| 3| 1| 9| 31|EN |Three.One |
2| | 0| 2| 2|EN |Two |
21| 2| 1| 7| 21|EN |Two.One |
211| 21| 2|10| 211|EN |Two.One.One |
213| 21| 2|12| 213|EN |Two.One.Three|
212| 21| 2|11| 212|EN |Two.One.Two |
22| 2| 1| 8| 22|EN |Two.Two |
请注意，每个深度的字母顺序都会迫使西班牙语产生不同的结果：
[编辑以修复Erwin识别的错误]
id |parent_id|tree_depth|id|category_id|lang_code|title |
---|---------|----------|--|-----------|---------|------------|
2| | 0|14| 2|ES |Dos |
22| 2| 1|20| 22|ES |Dos.Dos |
21| 2| 1|19| 21|ES |Dos.Uno |
212| 21| 2|23| 212|ES |Dos.Uno.Dos |
213| 21| 2|24| 213|ES |Dos.Uno.Tres|
211| 21| 2|22| 211|ES |Dos.Uno.Uno |
1| | 0|13| 1|ES |Uno |
12| 1| 1|17| 12|ES |Uno.Dos |
13| 1| 1|18| 13|ES |Uno.Tres |
11| 1| 1|16| 11|ES |Uno.Uno |
3| | 0|15| 3|ES |Tres |
31| 3| 1|21| 31|ES |Tres.Uno |
我尝试了许多方法，包括递归CTE，如https://www.postgresql.org/docs/12/queries-with.html，但似乎没有一个能解决不同语言的不同顺序问题。
有什么想法吗？
。。。预期的顺序应该符合树层次结构和英文字母顺序(在每个深度级别(，
额外的困难是category_lang(title, lang_code)没有定义为UNIQUE，所以我们需要在每个级别上按title和category_id(作为平局决胜局(进行排序，这对于动态数量的级别来说很难实现。复合类型的数组可以解决这个难题。
您显示的结果当前不符合您的要求。'根据英语排序规则，Three应该在Two之前排序。以下查询的结果实现了您的要求：
每个数据库创建一次：
CREATE TYPE title_id AS (title varchar(128), id int);
然后使用递归CTE根据其路径生成此复合类型的数组。
WITH RECURSIVE tree AS (
SELECT c.id AS cat_id, c.parent_id, c.tree_depth
, l.id AS lang_id, l.title, l.lang_code
, ARRAY[(l.title, l.category_id)::title_id] AS sort_arr
FROM category c
JOIN category_lang l ON l.category_id = c.id
AND l.lang_code = 'EN'
WHERE c.parent_id IS NULL -- root cat
UNION ALL
SELECT c.id AS cat_id, c.parent_id, c.tree_depth
, l.id AS lang_id, l.title, l.lang_code
, sort_arr || (l.title, l.category_id)::title_id
FROM tree t
JOIN category c ON c.parent_id = t.cat_id
JOIN category_lang l ON l.category_id = c.id
AND l.lang_code = t.lang_code
)
SELECT cat_id, parent_id, tree_depth, lang_id, title
FROM tree
ORDER BY sort_arr;
db<gt；小提琴这里
与更多解释和细节密切相关：
- 在PostgreSQL'；s Ltree模块
COLLATE
但这还不是全部。简单的解决方案按照数据库的默认排序规则进行排序，这似乎不适合不同的语言。
每种语言都有自己的排序规则，或者通常有几种规则，这取决于世界的地区和其他政治/文化规则。"；语言；不足以指定精确的排序规则。准确的地点很重要。Postgres使用COLLATE关键字实现了具有排序规则意识的排序。除了语言之外，您还必须存储实际的精确排序规则，并使用它进行正确排序。
此外，索引取决于确切的COLLATION。您可以考虑使用不同排序规则的多个分部索引。很多棘手的事情超出了这个问题的范围。参见：
- 如何选择国际数据库的排序规则
- PostgreSQL 9.1在select语句中使用collate
亚洲
您的触发器在并发写入之间的竞争条件下是不安全的。出于这个查询的目的，我们根本不需要持久化的
tree_depth。我们可以很容易地在rCTE中生成它。考虑删除列tree_depth和触发器。表
category_lang:中似乎缺少FK约束, FOREIGN KEY (category_id) REFERENCES category (id)考虑
text而不是varchar(n)和char(n)。参见：- 使用数据类型"；文本"；用于存储字符串
- 我应该为VARCHAR列添加任意长度限制吗
考虑使用
IDENTITY列而不是serial:- 自动递增表列
在Erwin用一个简单得多的解决方案回答之前，我自己制定了这种递归方法。它可以更好地插入虚拟root类别，这允许我们从单个入口点检索整个树。
create or replace function list_category_tree (
_category_id integer,
_lang_code char(2)
)
returns setof category
as $$
declare
_child_category category;
begin
-- return the passed category
return query
select * from category where id = _category_id;
-- loop over the passed category children
for _child_category in
select tc.*
from category tc
join category_lang tl on tl.category_id = tc.id and tl.lang_code = _lang_code
where tc.parent_id = _category_id
order by tl.title asc
loop
-- recursively look for every children childrens
return query
select * from list_category_tree(_child_category.id, _lang_code);
end loop;
end;
$$ language plpgsql;
一个简单的测试可以是
select *
from list_category_tree (0, 'EN') as tc
join category_lang tl on tl.category_id = tc.id and tl.lang_code = 'EN';
id |parent_id|tree_depth|id|category_id|lang_code|title |
---|---------|----------|--|-----------|---------|-------------|
1| | 0| 1| 1|EN |One |
11| 1| 1| 4| 11|EN |One.One |
13| 1| 1| 6| 13|EN |One.Three |
12| 1| 1| 5| 12|EN |One.Two |
2| | 0| 2| 2|EN |Two |
21| 2| 1| 7| 21|EN |Two.One |
211| 21| 2|10| 211|EN |Two.One.One |
213| 21| 2|12| 213|EN |Two.One.Three|
212| 21| 2|11| 212|EN |Two.One.Two |
22| 2| 1| 8| 22|EN |Two.Two |
3| | 0| 3| 3|EN |Three |
31| 3| 1| 9| 31|EN |Three.One |
select *
from list_category_tree (0, 'ES') as tc
join of_category_lang tl on tl.category_id = tc.id and tl.lang_code = 'ES';
id |parent_id|tree_depth|id|category_id|lang_code|title |
---|---------|----------|--|-----------|---------|------------|
2| 0| 1|14| 2|ES |Dos |
22| 2| 2|20| 22|ES |Dos.Dos |
21| 2| 2|19| 21|ES |Dos.Uno |
212| 21| 3|23| 212|ES |Dos.Uno.Dos |
213| 21| 3|24| 213|ES |Dos.Uno.Tres|
211| 21| 3|22| 211|ES |Dos.Uno.Uno |
3| 0| 1|15| 3|ES |Tres |
31| 3| 2|21| 31|ES |Tres.Uno |
1| 0| 1|13| 1|ES |Uno |
12| 1| 2|17| 12|ES |Uno.Dos |
13| 1| 2|18| 13|ES |Uno.Tres |
11| 1| 2|16| 11|ES |Uno.Uno |
已插入根节点作为
insert into of_category (parent_id, id) values
(null, 0),
(null, 1),
(null, 2),
(null, 3),
(1, 11),
(1, 12),
(1, 13),
(2, 21),
(2, 22),
(3, 31),
(21, 211),
(21, 212),
(21, 213);
-- lang_code = 'EN'
insert into of_category_lang (category_id, title, lang_code) values
(0, 'Root', 'EN'),
(1, 'One', 'EN'),
(2, 'Two', 'EN'),
(3, 'Three', 'EN'),
(11, 'One.One', 'EN'),
(12, 'One.Two', 'EN'),
(13, 'One.Three', 'EN'),
(21, 'Two.One', 'EN'),
(22, 'Two.Two', 'EN'),
(31, 'Three.One', 'EN'),
(211, 'Two.One.One', 'EN'),
(212, 'Two.One.Two', 'EN'),
(213, 'Two.One.Three', 'EN');
-- lang_code = 'ES'
insert into of_category_lang (category_id, title, lang_code) values
(0, 'Raíz', 'ES'),
(1, 'Uno', 'ES'),
(2, 'Dos', 'ES'),
(3, 'Tres', 'ES'),
(11, 'Uno.Uno', 'ES'),
(12, 'Uno.Dos', 'ES'),
(13, 'Uno.Tres', 'ES'),
(21, 'Dos.Uno', 'ES'),
(22, 'Dos.Dos', 'ES'),
(31, 'Tres.Uno', 'ES'),
(211, 'Dos.Uno.Uno', 'ES'),
(212, 'Dos.Uno.Dos', 'ES'),
(213, 'Dos.Uno.Tres', 'ES');