使用Postgres ltree存储产品类别的推荐方法是什么?
例如,我的列可以包含一个ltree路径,例如"1.2.3"
,其中1
、2
和3
是类别标签表中的外键,可以显示给用户:
categories
id | name
---+-----------
1 | Hardware
---+-----------
2 | Computers
---+-----------
3 | Video Cards
---+-----------
现在,对于给定的产品,我想选择它的类别并像
"Hardware > Computers > Video Cards"
那样具体化它。 最佳答案
第9.4+页:
SELECT p.id, string_agg(c.name, ' > ' ORDER BY t.ord) AS label
FROM product p
JOIN regexp_split_to_table(p.category::text, '[.]') WITH ORDINALITY t(category, ord) ON true
JOIN categories c ON c.id = t.category::int
GROUP BY p.id;
这一行:
regexp_split_to_table(p.category::text, '[.]') WITH ORDINALITY t(category, ord)
获取
ltree
列,然后将其分解为行,每个行对应于ltree
中的每个元素。WITH ORDINALITY
子句将在输出中添加一个行号,这里有aliasord
。该行号在string_agg()
函数中用于保持类别标签的正确顺序。如果您使用的是较旧版本的PG(9.0+),则(您应该升级或其他)应执行以下操作:
SELECT p.id, string_agg(c.name, ' > ' ORDER BY t.ord) AS label
FROM product p
JOIN generate_series(1, nlevel(p.category)) t(ord) ON true
JOIN categories c ON c.id = subltree(p.category, t.ord - 1, t.ord)::text::int
GROUP BY p.id;
这效率较低,因为必须为其中包含的每个单独元素(
ltree
)解析subltree(...)
。