Often, you wish to write a SQL question and fetch a hierarchy of knowledge, whose flat illustration could appear to be this:
SELECT id, parent_id, label
FROM t_directory;
The end result is likely to be:
|id |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |options | |7 |2 |plugins | |8 |2 |readme | |9 |8 |readme_eclipse.html| |10 |2 |src | |11 |2 |eclipse.exe |
Get the hierarchy with SQL
Now, you may run a recursive PostgreSQL question just like the beneath monster to show that right into a JSON doc:
WITH RECURSIVE
d1 (id, parent_id, identify) as (
SELECT id, parent_id, label
FROM t_directory
),
d2 AS (
SELECT d1.*, 0 AS stage
FROM d1
WHERE parent_id IS NULL
UNION ALL
SELECT d1.*, d2.stage + 1
FROM d1
JOIN d2 ON d2.id = d1.parent_id
),
d3 AS (
SELECT d2.*, jsonb_build_array() kids
FROM d2
WHERE stage = (SELECT max(stage) FROM d2)
UNION (
SELECT (branch_parent).*, jsonb_agg(branch_child)
FROM (
SELECT
branch_parent,
to_jsonb(branch_child) - 'stage' - 'parent_id' AS branch_child
FROM d2 branch_parent
JOIN d3 branch_child ON branch_child.parent_id = branch_parent.id
) department
GROUP BY department.branch_parent
UNION
SELECT d2.*, jsonb_build_array()
FROM d2
WHERE d2.id NOT IN (
SELECT parent_id FROM d2 WHERE parent_id IS NOT NULL
)
)
)
SELECT
jsonb_pretty(jsonb_agg(to_jsonb(d3) - 'stage' - 'parent_id')) AS tree
FROM d3
WHERE stage = 0;
I’ve given this question additionally as a solution to this Stack Overflow query. Some inspiration for the question on this weblog put up.
And behold, now we have a JSON tree:
[
{
"id": 1,
"name": "C:",
"children": [
{
"id": 2,
"name": "eclipse",
"children": [
{
"id": 7,
"name": "plugins"
},
{
"id": 4,
"name": "dropins"
},
{
"id": 8,
"name": "readme",
"children": [
{
"id": 9,
"name": "readme_eclipse.html"
}
]
},
{
"id": 11,
"identify": "eclipse.exe"
},
{
"id": 10,
"identify": "src"
},
{
"id": 5,
"identify": "options"
},
{
"id": 3,
"identify": "configuration"
}
]
}
]
}
]
However that’s fairly a beast of a SQL question, and maybe, you don’t want to do that with SQL within the first place.
Doing this with jOOQ 3.19
The truth is, ranging from jOOQ 3.19 and #12341, you are able to do this solely with jOOQ, utilizing a Collector.
Assuming you might have this consumer aspect illustration to your knowledge:
file File(int id, String identify, Checklist<File> kids) {}
Now, you’ll be able to write:
Checklist<File> end result =
ctx.choose(T_DIRECTORY.ID, T_DIRECTORY.PARENT_ID, T_DIRECTORY.LABEL)
.from(T_DIRECTORY)
.orderBy(T_DIRECTORY.ID)
.gather(Information.intoHierarchy(
r -> r.value1(),
r -> r.value2(),
(r, l) -> new File(r.value1(), r.value3(), l)
));
Be aware, relying on how sturdy kind inference works in your favour or not, you may must trace sorts of the
(e, l) -> ...lambda
That’s it! While you print the end 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 happen to desire JSON output, simply use Jackson, or no matter, to serialise your knowledge as follows:
new ObjectMapper()
.writerWithDefaultPrettyPrinter()
.writeValue(System.out, end 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,
"identify" : "src"
}, {
"id" : 11,
"identify" : "eclipse.exe"
} ]
} ]
} ]
Very cool, huh?
Don’t use jOOQ? No drawback, simply copy this Collector:
The above isn’t actually jOOQ particular magic. You’ll be able to simply copy the next Collector from jOOQ to attain the identical factor together with your pure Java code:
// Presumably, seize the Checklist<E> kind in a brand new kind variable in case you
// have hassle with kind inference
public static ultimate <Ok, E, R> Collector<R, ?, Checklist<E>> intoHierarchy(
Perform<? tremendous R, ? extends Ok> keyMapper,
Perform<? tremendous R, ? extends Ok> parentKeyMapper,
BiFunction<? tremendous R, ? tremendous Checklist<E>, ? extends E> recordMapper
) {
return intoHierarchy(
keyMapper, parentKeyMapper, recordMapper, ArrayList::new
);
}
public static ultimate <
Ok, E, C extends Assortment<E>, R
> Collector<R, ?, Checklist<E>> intoHierarchy(
Perform<? tremendous R, ? extends Ok> keyMapper,
Perform<? tremendous R, ? extends Ok> parentKeyMapper,
BiFunction<? tremendous R, ? tremendous C, ? extends E> recordMapper,
Provider<? extends C> collectionFactory
) {
file Tuple3<T1, T2, T3>(T1 t1, T2 t2, T3 t3) {}
return Collectors.collectingAndThen(
Collectors.toMap(keyMapper, r -> {
C e = collectionFactory.get();
return new Tuple3<R, C, E>(r, e, recordMapper.apply(r, e));
}),
m -> {
Checklist<E> r = new ArrayList<>();
m.forEach((okay, v) -> {
Ok guardian = parentKeyMapper.apply(v.t1());
E baby = v.t3();
if (m.containsKey(guardian))
m.get(guardian).t2().add(baby);
else
r.add(baby);
});
return r;
}
);
}
With this collector, and the next varieties / knowledge:
file Flat(int id, int parentId, String identify) {}
file Hierarchical(int id, String identify, Checklist<Hierarchical> kids) {}
Checklist<Flat> knowledge = Checklist.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, "options"),
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")
);
Now you can create the identical hierarchy once more, utilizing the Collector instantly on the listing:
Checklist<Hierarchical> end result =
knowledge.stream().gather(intoHierarchy(
e -> e.id(),
e -> e.parentId(),
(e, l) -> new Hierarchical(e.id(), e.identify(), l)
));
Be aware, relying on how sturdy kind inference works in your favour or not, you may must once more trace sorts of the
(e, l) -> ...lambda
A extra advanced jOOQ instance
In jOOQ, all outcomes, together with nested collections (e.g. these produced by MULTISET) could be collected, so when you’ve got a nested hierarchy, akin to feedback on a weblog put up, simply gather them with jOOQ.
Assuming this schema:
CREATE TABLE put up (
id INT PRIMARY KEY,
title TEXT
);
CREATE TABLE remark (
id INT PRIMARY KEY,
parent_id INT REFERENCES remark,
post_id INT REFERENCES put up,
textual content TEXT
);
INSERT INTO put up
VALUES
(1, 'Helo'),
(2, 'World');
INSERT INTO remark
VALUES
(1, NULL, 1, 'You misspelled "Hiya"'),
(2, 1, 1, 'Thanks, will repair quickly'),
(3, 2, 1, 'Nonetheless not mounted'),
(4, NULL, 2, 'Impeccable weblog put up, thanks');
You possibly can write a question like this:
file Submit(int id, String title, Checklist<Remark> feedback) {}
file Remark(int id, String textual content, Checklist<Remark> replies) {}
Checklist<Submit> end result =
ctx.choose(
POST.ID,
POST.TITLE,
multiset(
choose(COMMENT.ID, COMMENT.PARENT_ID, COMMENT.TEXT)
.from(COMMENT)
.the place(COMMENT.POST_ID.eq(POST.ID))
).convertFrom(r -> r.gather(intoHierarchy(
r -> r.value1(),
r -> r.value2(),
(e, l) -> new Remark(r.value1(), r.value3(), l)
))
)
.from(POST)
.orderBy(POST.ID)
.fetch(mapping(Submit::new));
All of that is type-safe, as at all times with jOOQ!
Now, take a look at what this prints, when serialised 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",
"feedback" : [ {
"id" : 4,
"text" : "Impeccable blog post, thanks"
} ]
} ]
Be aware, if you happen to solely wish to present a subtree, or a tree up till a sure depth, you’ll be able to nonetheless run a hierarchical question in your
MULTISETsubquery utilizingWITH RECURSIVEorCONNECT BY.
Conclusion
Collector is a a lot underrated API within the JDK. Any JDK Assortment could be was a Stream and its parts could be collected. In jOOQ, a ResultQuery is an Iterable, which additionally provides a handy gather() methodology (it simply executes the question, streams outcomes, and collects data into your Collector).
Our practical library jOOλ has many further collectors in its Agg class, e.g. for:
- Bitwise aggregation
- Statistical aggregation, like normal deviation, correlation, percentiles, and so forth.
Amassing issues right into a hierarchy isn’t actually that particular. It’s simply one other collector, which I’m certain, you’ll be utilizing rather more often to any extent further!
