CODEWITHSUNDEEP
javasqlpostgresqljooq
MG_Bautista
MG_Bautista

Reputation: 2653

JOOQ Query hierarchy

i have a problem with a table call "menu". this table save the parent menu item with pid null and their child's with pid = "parent id".

the table structure is:

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

And the data is:

enter image description here

I need build a JOOQ query to retrieve the data in tree mode using the field seq, id and pid.

I need Help to get query result:

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

The expected result above takes into account the seq field and the field pid

Upvotes: 3

Views: 1480

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220877

Doing it with SQL

For my answer, I'm going to assume that seq is being used to order siblings in your menu hierarchy, and that your sample data is wrong (no two siblings may have the same seq value, i.e. there should be UNIQUE (pid, seq). So, I'll work with this sample data (INT IDs for simplicity):

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

You are going to need a recursive query for that, by using the WITH clause. In 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;

The query output can be seen here. It is:

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       |

There are, of course, other ways to achieve the same result. Column explanations:

  • id: The original menu item id
  • path: The path leading to any given menu item (an array of concatenated seq value, assuming they are unique per pid)
  • name: The original name of a menu item
  • level: The recursion or nesting level (useful for padding)
  • display: The padded display of the menu item, as per your question

Doing it with jOOQ

Now, you simply have to translate the above to a jOOQ query.

Assuming these static imports (as always):

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

Upvotes: 7

Related Questions