logo标签列表

Postgres WITH RECURSIVE CTE：按照流行度对子节点进行排序/排序，同时保留树形结构（父节点始终在子节点之上）

sqlpostgresqlrecursionparent-childcommon-table-expression
8
我正在建立一个论坛，非常类似于Reddit/Slashdot，即：
  • 无限的回复嵌套级别
  • 热门评论（按赞数/投票排序）将上升到顶部（在其自己的嵌套/深度级别内），但树形结构需要保留（父级始终直接显示在子级上方）
这是一个示例表格和数据：
DROP TABLE IF EXISTS "comments";
CREATE TABLE comments (
  id BIGINT PRIMARY KEY,
  parent_id BIGINT,
  body TEXT NOT NULL,
  like_score BIGINT,
  depth BIGINT
);

INSERT INTO comments VALUES (  0, NULL, 'Main top of thread post', 5 , 0 );

INSERT INTO comments VALUES (  1, 0, 'comment A', 5 , 1 );
INSERT INTO comments VALUES (  2, 1,    'comment A.A', 3, 2 );
INSERT INTO comments VALUES (  3, 1,    'comment A.B', 1, 2 );
INSERT INTO comments VALUES (  9, 3,    'comment A.B.A', 10, 3 );
INSERT INTO comments VALUES ( 10, 3,    'comment A.B.B', 5, 3 );
INSERT INTO comments VALUES ( 11, 3,    'comment A.B.C', 8, 3 );
INSERT INTO comments VALUES (  4, 1,    'comment A.C', 5, 2 );

INSERT INTO comments VALUES ( 5, 0, 'comment B', 10, 1 );
INSERT INTO comments VALUES ( 6, 5, 'comment B.A', 7, 2 );
INSERT INTO comments VALUES ( 7, 5, 'comment B.B', 5, 2 );
INSERT INTO comments VALUES ( 8, 5, 'comment B.C', 2, 2 );

这是我目前想出的递归查询，但我无法弄清楚如何排序子元素并保留树形结构（父级应始终在子级上方）...

WITH RECURSIVE tree AS (
  SELECT
    ARRAY[]::BIGINT[] AS sortable,
    id,
    body,
    like_score,
    depth
  FROM "comments"
  WHERE parent_id IS NULL

  UNION ALL

  SELECT
    tree.sortable ||  "comments".like_score || "comments".id,
    "comments".id,
    "comments".body,
    "comments".like_score,
    "comments".depth
  FROM "comments", tree
  WHERE "comments".parent_id = tree.id
)
SELECT * FROM tree
ORDER BY sortable DESC

这将输出...

+----------------------------------------------------------+
|sortable      |id|body                   |like_score|depth|
+----------------------------------------------------------+
|{10,5,7,6}    |6 |comment B.A            |7         |2    |
|{10,5,5,7}    |7 |comment B.B            |5         |2    |
|{10,5,2,8}    |8 |comment B.C            |2         |2    |
|{10,5}        |5 |comment B              |10        |1    |
|{5,1,5,4}     |4 |comment A.C            |5         |2    |
|{5,1,3,2}     |2 |comment A.A            |3         |2    |
|{5,1,1,3,10,9}|9 |comment A.B.A          |10        |3    |
|{5,1,1,3,8,11}|11|comment A.B.C          |8         |3    |
|{5,1,1,3,5,10}|10|comment A.B.B          |5         |3    |
|{5,1,1,3}     |3 |comment A.B            |1         |2    |
|{5,1}         |1 |comment A              |5         |1    |
|              |0 |Main top of thread post|5         |0    |
+----------------------------------------------------------+

请注意，“comment B”、“comment A”和“主串顶部贴文”在它们的子评论下面？ 我如何保持上下文顺序？即，我想要的输出是：

+----------------------------------------------------------+
|sortable      |id|body                   |like_score|depth|
+----------------------------------------------------------+
|              |0 |Main top of thread post|5         |0    |
|{10,5}        |5 |comment B              |10        |1    |
|{10,5,7,6}    |6 |comment B.A            |7         |2    |
|{10,5,5,7}    |7 |comment B.B            |5         |2    |
|{10,5,2,8}    |8 |comment B.C            |2         |2    |
|{5,1}         |1 |comment A              |5         |1    |
|{5,1,5,4}     |4 |comment A.C            |5         |2    |
|{5,1,3,2}     |2 |comment A.A            |3         |2    |
|{5,1,1,3}     |3 |comment A.B            |1         |2    |
|{5,1,1,3,10,9}|9 |comment A.B.A          |10        |3    |
|{5,1,1,3,8,11}|11|comment A.B.C          |8         |3    |
|{5,1,1,3,5,10}|10|comment A.B.B          |5         |3    |
+----------------------------------------------------------+

我希望用户能够按多种方式排序：

  • 最受欢迎的在前
  • 最不受欢迎的在前
  • 最新发布的在前
  • 最早发布的在前
  • 等等

...但在所有情况下，父级别需要显示在子级别之上。这里只是以“like_score”为例，我应该能够从中弄清其余部分。

我花了很多时间在网络上进行研究并尝试一些方法，感觉离成功已经很接近了，但无法解决最后一个问题。

2个回答
6

1.

tree.sortable ||  -"comments".like_score || "comments".id
                  ^
                 /|\
                  |
                  |

2.

ORDER BY sortable  

WITH RECURSIVE tree AS (
  SELECT
    ARRAY[]::BIGINT[] AS sortable,
    id,
    body,
    like_score,
    depth
  FROM "comments"
  WHERE parent_id IS NULL

  UNION ALL

  SELECT
    tree.sortable ||  -"comments".like_score || "comments".id,
    "comments".id,
    "comments".body,
    "comments".like_score,
    "comments".depth
  FROM "comments", tree
  WHERE "comments".parent_id = tree.id
)
SELECT * FROM tree
ORDER BY sortable 

+-------------------+----+-------------------------+------------+-------+
| sortable          | id | body                    | like_score | depth |
+-------------------+----+-------------------------+------------+-------+
| (null)            | 0  | Main top of thread post | 5          | 0     |
+-------------------+----+-------------------------+------------+-------+
| {-10,5}           | 5  | comment B               | 10         | 1     |
+-------------------+----+-------------------------+------------+-------+
| {-10,5,-7,6}      | 6  | comment B.A             | 7          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-10,5,-5,7}      | 7  | comment B.B             | 5          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-10,5,-2,8}      | 8  | comment B.C             | 2          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1}            | 1  | comment A               | 5          | 1     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-5,4}       | 4  | comment A.C             | 5          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-3,2}       | 2  | comment A.A             | 3          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-1,3}       | 3  | comment A.B             | 1          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-1,3,-10,9} | 9  | comment A.B.A           | 10         | 3     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-1,3,-8,11} | 11 | comment A.B.C           | 8          | 3     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-1,3,-5,10} | 10 | comment A.B.B           | 5          | 3     |
+-------------------+----+-------------------------+------------+-------+
太棒了，谢谢！之前我尝试过一些 0-field 的东西，但是它们放错位置了，ASC/DESC 顺序也不对...离成功很近，但又很远，哈哈！所以我想现在 || "comments".id 是多余的，我可以把它删掉吗？非常感谢你非常清晰地解释了它们之间的区别，并包括了样本输出，这让理解起来非常容易。 - LaVache
1
不客气 :-) 我不会删除 id，因为在同一级别中，它是相等的 like_score 的决定者。 - David דודו Markovitz
哦，没错，不知道我为什么认为我不再需要它了。还有你是如何生成 ASCII 输出表的？你使用生成它的 SQL GUI 吗？ - LaVache
1
请将以下程序相关内容从英语翻译成中文。只返回已翻译的文本：http://www.tablesgenerator.com/text_tables（文件->粘贴表格数据->加载，双击底部的ASCII表格，复制，粘贴到SO并使用Ctrl-K将文本更改为格式化代码） - David דודו Markovitz
0

请检查：

WITH RECURSIVE tree AS (
  SELECT
    ARRAY[]::BIGINT[] AS sortable,
    id,
    body,
    like_score,
    depth,
    lpad(id::text, 2, '0') as path
  FROM "comments"
  WHERE parent_id IS NULL

  UNION ALL

  SELECT
    tree.sortable ||  "comments".like_score || "comments".id,
    "comments".id,
    "comments".body,
    "comments".like_score,
    "comments".depth,
    tree.path || '/' || lpad("comments".id::text, 2, '0') as path
  FROM "comments", tree
  WHERE "comments".parent_id = tree.id
)
SELECT * FROM tree
ORDER BY path

请注意，您可以将 lpad 中的参数 2 替换为您想要的任何数字位数。
请根据所需结果验证您的结果。 - David דודו Markovitz
@DuduMarkovitz 你是对的。你的答案是正确的。 - wind39
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接