JOOQ查询层次结构

Question

我需要翻译一个和 IT 技术相关的问题。这个问题是关于一个名为 "menu" 的表的。这个表保存了父菜单项，其 pid 为 null，以及它们的子菜单项，pid 为 "parent id"。

该表的结构如下：

CREATE TABLE security.menu (
  id UUID NOT NULL,
  pid UUID DEFAULT NULL,
  url VARCHAR(100) DEFAULT NULL,
  name VARCHAR(50)DEFAULT NULL,
  seq NUMERIC DEFAULT NULL,
  state NUMERIC DEFAULT 1,
  created_at TIMESTAMP DEFAULT NULL,
  updated_at TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (id)
);

以下是数据:

我需要使用字段seq、id和pid构建一个JOQ查询，以树状模式检索数据。

我需要帮助获取查询结果:

-Menu 1
     -Sub Menu 1
-Menu 2
-Menu 3
-Menu 1

上述期望结果考虑了seq字段和pid字段。

- MG_Bautista

---

嗨，这是一个很好的习惯，你可以发布你尝试过的代码，这样大家就能从那里帮助你了。展示一些代码示例，让社区能更好地帮助你！ - undefined

你的seq内容真的正确吗？为什么Menu 1和Menu 2有相同的seq值？我猜想seq用于对任何给定菜单项的子项进行排序... - undefined

@LukasEder，抱歉，Menu 2的seq有误。 - undefined

@MG_Bautista: 不用担心 :) 或许你应该添加一个 UNIQUE (pid, seq) 键！ - undefined

Answer 1

使用SQL完成

对于我的回答，我假设seq是用于对菜单层次结构中的兄弟节点进行排序的，并且您提供的示例数据是错误的（没有两个兄弟节点可以具有相同的seq值，即应该有UNIQUE (pid，seq)）。因此，我将使用以下示例数据进行操作（使用INT ID以简化问题）：

INSERT INTO menu (id, pid, name, seq)
VALUES 
  (1, null, 'Menu 1', 1), 
  (2, null, 'Menu 2', 2), 
  (3, null, 'Menu 3', 3), 
  (4, 1, 'Sub Menu 1', 1), 
  (5, null, 'Menu 1', 9);

你需要使用WITH子句来进行递归查询。在SQL中：

WITH RECURSIVE m AS (
  SELECT 
    id, 
    ARRAY[seq] AS path, 
    name, 1 AS level, 
    '- ' || name AS display
  FROM menu
  WHERE pid IS NULL
  UNION ALL 
  SELECT 
    menu.id, 
    path || seq, 
    menu.name, 
    m.level + 1 AS level, 
    repeat('  ', m.level) || '- ' || menu.name
  FROM menu JOIN m ON m.id = menu.pid
)
SELECT *
FROM m
ORDER BY path;

这里可以查看查询结果，它是：

id |path  |name       |level |display        |
---|------|-----------|------|---------------|
1  |{1}   |Menu 1     |1     |- Menu 1       |
4  |{1,1} |Sub Menu 1 |2     |  - Sub Menu 1 |
2  |{2}   |Menu 2     |1     |- Menu 2       |
3  |{3}   |Menu 3     |1     |- Menu 3       |
5  |{9}   |Menu 1     |1     |- Menu 1       |

当然，还有其他的方法可以达到同样的效果。列说明：

- id：原始菜单项ID - path：通向任何给定菜单项的路径（一个串联的seq值数组，假设它们在每个pid下是唯一的） - name：菜单项的原始名称 - level：递归或嵌套级别（对于填充很有用） - display：根据你的问题填充菜单项的显示

使用jOOQ完成此操作:

现在，你只需要将上面的内容翻译成jOOQ查询。

假设这些静态导入(像平常一样)：

import static org.jooq.impl.DSL.*;
import static com.example.generated.Table.*;

as follows:

Field<Integer[]> path = array(MENU.SEQ).as("path");
Field<Integer> level = inline(1).as("level");
Field<String> display = inline("- ").concat(MENU.NAME).as("display");

Table<?> m = name("m").as(
  select(MENU.ID, path, MENU.NAME, level, display)
 .from(MENU)
 .where(MENU.PID.isNull())
 .unionAll(
  select(
    MENU.ID,
    PostgresDSL.arrayAppend(path, MENU.SEQ),
    MENU.NAME,
    level.add(inline(1)),
    repeat(inline("  "), level).concat(inline("- ")).concat(MENU.NAME))
 .from(MENU)
 .join(table(name("m"))).on(field(name("m", "id"), Integer.class).eq(MENU.PID)))
);

ctx.selectFrom(m).orderBy(path).fetch();

- Lukas Eder