java - JOOQ 查询层次结构

Question

我对表调用“ 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)
);

数据是：

我需要构建一个 JOOQ 查询来使用字段检索树模式中的数据seq，id并且pid。

我需要帮助来获取查询结果：

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

上面的预期结果考虑了 seq 字段和字段 pid

标签： javasqlpostgresqljooq

Answer 1

用 SQL 来做

对于我的回答，我将假设它seq被用来在您的菜单层次结构中订购兄弟姐妹，并且您的示例数据是错误的（没有两个兄弟姐妹可能具有相同的seq值，即应该有UNIQUE (pid, seq)。所以，我会工作使用此示例数据（INT为简单起见 IDs）：

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 clause. 在 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值的数组，假设它们是唯一的 per pid）
• name：菜单项的原始名称
• level：递归或嵌套级别（用于填充）
• display：根据您的问题，菜单项的填充显示

用 jOOQ 做

现在，您只需要将上述内容转换为 jOOQ 查询。

假设这些静态导入（一如既往）：

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

如下：

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();