Occasionally, you want to write a sql Query and fetch a hierchy of data, whose flat representation may look like this:
SELECT id, parent_id, label
FROM t_directory;
The result might be:
|id |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |features | |7 |2 |plugins | |8 |2 |readme | |9 |8 |readme_eclipse.html| |10 |2 |src | |11 |2 |eclipse.exe |
Get the Hierchy with Sql
Now, you could run a recursive postgresql queted the below monster to turn that into a json document:
WITH RECURSIVE
d1 (id, parent_id, name) as (
SELECT id, parent_id, label
FROM t_directory
),
d2 AS (
SELECT d1.*, 0 AS level
FROM d1
WHERE parent_id IS NULL
UNION ALL
SELECT d1.*, d2.level + 1
FROM d1
JOIN d2 ON d2.id = d1.parent_id
),
d3 AS (
SELECT d2.*, null::jsonb children
FROM d2
WHERE level = (SELECT max(level) FROM d2)
UNION (
SELECT
(branch_parent).*,
jsonb_strip_nulls(
jsonb_agg(branch_child - 'parent_id' - 'level'
ORDER BY branch_child->>'name'
) FILTER (
WHERE branch_child->>'parent_id' = (branch_parent).id::text
)
)
FROM (
SELECT
branch_parent,
to_jsonb(branch_child) AS branch_child
FROM d2 branch_parent
JOIN d3 branch_child
ON branch_child.level = branch_parent.level + 1
) branch
GROUP BY branch_parent
)
)
SELECT
jsonb_pretty(jsonb_agg(to_jsonb(d3) - 'parent_id' - 'level')) AS tree
FROM d3
WHERE level = 0;
I’ve given this query also as an answer to this stack overflow question. Some inspiration for the Query in this blog post.
And behold, we have a json tree:
[
{
"id": 1,
"name": "C:",
"children": [
{
"id": 2,
"name": "eclipse",
"children": [
{
"id": 3,
"name": "configuration"
},
{
"id": 4,
"name": "dropins"
},
{
"id": 11,
"name": "eclipse.exe"
},
{
"id": 5,
"name": "features"
},
{
"id": 7,
"name": "plugins"
},
{
"id": 8,
"name": "readme",
"children": [
{
"id": 9,
"name": "readme_eclipse.html"
}
]
},
{
"id": 10,
"name": "src"
}
]
}
]
}
]
But that’s quite a beast of a SQL Query, and Perhaps, You don’t need to do this with sql in the first place.
Doing this with Jooq 3.19
In Fact, Starting From Jooq 3.19 and #12341, You can do this entrely with joooq, using a Collector,
Assuming you have this client side representation for your data:
record File(int id, String name, List children) {}
Now, you can write:
List result =
ctx.select(T_DIRECTORY.ID, T_DIRECTORY.PARENT_ID, T_DIRECTORY.LABEL)
.from(T_DIRECTORY)
.orderBy(T_DIRECTORY.ID)
.collect(Records.intoHierarchy(
r -> r.value1(),
r -> r.value2(),
r -> new File(r.value1(), r.value3(), new ArrayList<>()),
(p, c) -> p.children().add(c)
));
That’s it! When you print the result, you’ll get:
[
File[id=1, name=C:, children=[
File[id=2, name=eclipse, children=[
File[id=3, name=configuration, children=[]],
File[id=4, name=dropins, children=[]],
File[id=5, name=features, children=[]],
File[id=7, name=plugins, children=[]],
File[id=8, name=readme, children=[
File[id=9, name=readme_eclipse.html, children=[]]
]],
File[id=10, name=src, children=[]],
File[id=11, name=eclipse.exe, children=[]]
]]
]]
]
Or, if you prefer json output, just use jackson, or wheatever, to serialize your data as follows:
new ObjectMapper()
.writerWithDefaultPrettyPrinter()
.writeValue(System.out, result);
And now, you’re getting:
[ {
"id" : 1,
"name" : "C:",
"children" : [ {
"id" : 2,
"name" : "eclipse",
"children" : [ {
"id" : 3,
"name" : "configuration"
}, {
"id" : 4,
"name" : "dropins"
}, {
"id" : 5,
"name" : "features"
}, {
"id" : 7,
"name" : "plugins"
}, {
"id" : 8,
"name" : "readme",
"children" : [ {
"id" : 9,
"name" : "readme_eclipse.html"
} ]
}, {
"id" : 10,
"name" : "src"
}, {
"id" : 11,
"name" : "eclipse.exe"
} ]
} ]
} ]
Very cool, huh?
Don’t Use Jooq? No problem, just copy this collector:
The Above Isn’t Really Jooq Specific Magic. You can just copy the following Collector From jooq to achieve the same thing with your pure java code:
public static final
Collector> intoHierarchy(
Function super R, ? extends K> keyMapper,
Function super R, ? extends K> parentKeyMapper,
Function super R, ? extends E> nodeMapper,
BiConsumer super E, ? super E> parentChildAppender
) {
return Collectors.collectingAndThen(
Collectors.toMap(keyMapper, r -> new SimpleImmutableEntry(
r, nodeMapper.apply(r)
)),
m -> {
List r = new ArrayList<>();
m.forEach((k, v) -> {
Entry parent = m.get(
parentKeyMapper.apply(v.getKey())
);
if (parent != null)
parentChildAppender.accept(
parent.getValue(), v.getValue()
);
else
r.add(v.getValue());
});
return r;
}
);
}
With this collector, and the following types / data:
record Flat(int id, int parentId, String name) {}
record Hierarchical(int id, String name, List children) {}
List data = List.of(
new Flat(1, 0, "C:"),
new Flat(2, 1, "eclipse"),
new Flat(3, 2, "configuration"),
new Flat(4, 2, "dropins"),
new Flat(5, 2, "features"),
new Flat(7, 2, "plugins"),
new Flat(8, 2, "readme"),
new Flat(9, 8, "readme_eclipse.html"),
new Flat(10, 2, "src"),
new Flat(11, 2, "eclipse.exe")
);
You can now create the same hierchy again, using the Collector directly on the list:
List result =
data.stream().collect(intoHierarchy(
e -> e.id(),
e -> e.parentId(),
e -> new Hierarchical(e.id(), e.name(), new ArrayList<>()),
(p, c) -> p.children().add(c)
));
An alternative api
A Previous Version of this blog post used an alternative api design for the Collector,
public static final Collector> intoHierarchy(
Function super R, ? extends K> keyMapper,
Function super R, ? extends K> parentKeyMapper,
BiFunction super R, ? super List, ? extends E> recordMapper
) {
record Tuple3(T1 t1, T2 t2, T3 t3) {}
return Collectors.collectingAndThen(
Collectors.toMap(keyMapper, r -> {
List e = new ArrayList<>();
return new Tuple3(r, e, recordMapper.apply(r, e));
}),
m -> {
List r = new ArrayList<>();
m.forEach((k, v) -> {
K parent = parentKeyMapper.apply(v.t1());
E child = v.t3();
if (m.containsKey(parent))
m.get(parent).t2().add(child);
else
r.add(child);
});
return r;
}
);
}
This can lead to more compact usages in client code:
List result =
data.stream().collect(intoHierarchy(
e -> e.id(),
e -> e.parentId(),
(e, l) -> new Hierarchical(e.id(), e.name(), l)
));
However, it relieves on type infection of the target type (see jep 101). As soon as you dohan’t the target type anymore, infererance falls appart, so this won’t compile:
List> result =
data.stream().collect(intoHierarchy(
e -> e.id(),
e -> e.parentId(),
(e, l) -> new Hierarchical(e.id(), e.name(), l)
));
This design would be quite impractical for users, especially when written complex jooq queries, so it was rejected.
A more complex jooq example
In Jooq, All Results, Including Nested Collections (Eg Thos Produce by MULTISET) Can be collected, so if you have a nested hierchy, such as comments on a blog post, just collect them with jooq.
Assuming this Schema:
CREATE TABLE post (
id INT PRIMARY KEY,
title TEXT
);
CREATE TABLE comment (
id INT PRIMARY KEY,
parent_id INT REFERENCES comment,
post_id INT REFERENCES post,
text TEXT
);
INSERT INTO post
VALUES
(1, 'Helo'),
(2, 'World');
INSERT INTO comment
VALUES
(1, NULL, 1, 'You misspelled "Hello"'),
(2, 1, 1, 'Thanks, will fix soon'),
(3, 2, 1, 'Still not fixed'),
(4, NULL, 2, 'Impeccable blog post, thanks');
You could write a query like this:
record Post(int id, String title, List comments) {}
record Comment(int id, String text, List replies) {}
List result =
ctx.select(
POST.ID,
POST.TITLE,
multiset(
select(COMMENT.ID, COMMENT.PARENT_ID, COMMENT.TEXT)
.from(COMMENT)
.where(COMMENT.POST_ID.eq(POST.ID))
).convertFrom(r -> r.collect(intoHierarchy(
r -> r.value1(),
r -> r.value2(),
r -> new Comment(r.value1(), r.value3(), new ArrayList<>()),
(p, c) -> p.replies().add(c)
)))
)
.from(POST)
.orderBy(POST.ID)
.fetch(mapping(Post::new));
All of this is type-safe, as always with Jooq!
Now, check out what this prints, when serialized with jackson:
[ {
"id" : 1,
"title" : "Helo",
"comments" : [ {
"id" : 1,
"text" : "You misspelled \"Hello\"",
"replies" : [ {
"id" : 2,
"text" : "Thanks, will fix soon",
"replies" : [ {
"id" : 3,
"text" : "Still not fixed"
} ]
} ]
} ]
}, {
"id" : 2,
"title" : "World",
"comments" : [ {
"id" : 4,
"text" : "Impeccable blog post, thanks"
} ]
} ]
Note, if you only want to show a subtree, or a tree up until a certain depth, you can still run a hierchical Query in your
MULTISETsubquery usingWITH RECURSIVEorCONNECT BY,
Conclusion
Collector is a much underrated api in the jdk. Any jdk Collection can be turned into a Stream And its elements can be collected. In Jooq, A ResultQuery is an IterableWhich also offers a convenient collect() Method (It just executes the Query, Streams Results, and Collects Records Into Your Collector,
Our Functional Library jooλ has many additional collectors in its Agg Class, eg for:
- BitWise Aggregation
- Statistical Aggregation, Like Standard Deviation, Correlation, Percentles, etc.
Collecting things into a hierchy isn’T really that special. It’s just another collector, which i’m sure, you’ll be using much more frequently from now on!
Ramesh Ghorai is the founder of www.livenewsblogger.com, a platform dedicated to delivering exclusive live news from across the globe and the local market. With a passion for covering diverse topics, he ensures readers stay updated with the latest and most reliable information. Over the past two years, Ramesh has also specialized in writing top software reviews, partnering with various software companies to provide in-depth insights and unbiased evaluations. His mission is to combine news reporting with valuable technology reviews, helping readers stay informed and make smarter choices.
- NewsBloggerHubhttps://livenewsblogger.com/author/newsbloggerhub/
- NewsBloggerHubhttps://livenewsblogger.com/author/newsbloggerhub/
- NewsBloggerHubhttps://livenewsblogger.com/author/newsbloggerhub/
- NewsBloggerHubhttps://livenewsblogger.com/author/newsbloggerhub/
