Often, you need to write a SQL question and fetch a hierarchy of knowledge, whose flat illustration could seem like this:
SELECT id, parent_id, label
FROM t_directory;
The end result may 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 could possibly 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 degree
FROM d1
WHERE parent_id IS NULL
UNION ALL
SELECT d1.*, d2.degree + 1
FROM d1
JOIN d2 ON d2.id = d1.parent_id
),
d3 AS (
SELECT d2.*, jsonb_build_array() kids
FROM d2
WHERE degree = (SELECT max(degree) FROM d2)
UNION (
SELECT (branch_parent).*, jsonb_agg(branch_child)
FROM (
SELECT
branch_parent,
to_jsonb(branch_child) - 'degree' - '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) - 'degree' - 'parent_id')) AS tree
FROM d3
WHERE degree = 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, we have now 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
Actually, ranging from jOOQ 3.19 and #12341, you are able to do this solely with jOOQ, utilizing a Collector.
Assuming you could have this consumer aspect illustration on your knowledge:
document File(int id, String identify, Checklist<File> kids) {}
Now, you possibly can write:
Checklist<File> end result =
ctx.choose(T_DIRECTORY.ID, T_DIRECTORY.PARENT_ID, T_DIRECTORY.LABEL)
.from(T_DIRECTORY)
.orderBy(T_DIRECTORY.ID)
.accumulate(Data.intoHierarchy(
r -> r.value1(),
r -> r.value2(),
(r, l) -> new File(r.value1(), r.value3(), l)
));
Notice, relying on how sturdy kind inference works in your favour or not, you would possibly have to trace varieties of the
(e, l) -> ...lambda
That’s it! If 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, in case you 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 possibly can simply copy the next Collector from jOOQ to attain the identical factor together with your pure Java code:
// Probably, 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(
Operate<? tremendous R, ? extends Ok> keyMapper,
Operate<? 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(
Operate<? tremendous R, ? extends Ok> keyMapper,
Operate<? tremendous R, ? extends Ok> parentKeyMapper,
BiFunction<? tremendous R, ? tremendous C, ? extends E> recordMapper,
Provider<? extends C> collectionFactory
) {
document 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 dad or mum = parentKeyMapper.apply(v.t1());
E baby = v.t3();
if (m.containsKey(dad or mum))
m.get(dad or mum).t2().add(baby);
else
r.add(baby);
});
return r;
}
);
}
With this collector, and the next varieties / knowledge:
document Flat(int id, int parentId, String identify) {}
document 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().accumulate(intoHierarchy(
e -> e.id(),
e -> e.parentId(),
(e, l) -> new Hierarchical(e.id(), e.identify(), l)
));
Notice, relying on how sturdy kind inference works in your favour or not, you would possibly have to once more trace varieties of the
(e, l) -> ...lambda
A extra complicated jOOQ instance
In jOOQ, all outcomes, together with nested collections (e.g. these produced by MULTISET) might be collected, so you probably have a nested hierarchy, resembling feedback on a weblog put up, simply accumulate 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 "Hey"'),
(2, 1, 1, 'Thanks, will repair quickly'),
(3, 2, 1, 'Nonetheless not mounted'),
(4, NULL, 2, 'Impeccable weblog put up, thanks');
You may write a question like this:
document Publish(int id, String title, Checklist<Remark> feedback) {}
document Remark(int id, String textual content, Checklist<Remark> replies) {}
Checklist<Publish> 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.accumulate(intoHierarchy(
r -> r.value1(),
r -> r.value2(),
// The ultimate model could or could not require specific param varieties
// on this specific lambda:
(e, l) -> new Remark(r.value1(), r.value3(), l)
))
)
.from(POST)
.orderBy(POST.ID)
.fetch(mapping(Publish::new));
All of that is type-safe, as at all times with jOOQ!
Now, try 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"
} ]
} ]
Notice, in case you solely need to present a subtree, or a tree up till a sure depth, you possibly can 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 might be became a Stream and its components might be collected. In jOOQ, a ResultQuery is an Iterable, which additionally gives a handy accumulate() technique (it simply executes the question, streams outcomes, and collects information into your Collector).
Our practical library jOOλ has many extra collectors in its Agg class, e.g. for:
- Bitwise aggregation
- Statistical aggregation, like commonplace deviation, correlation, percentiles, and so forth.
Gathering 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 continuously any further!
