@Override
    public TreeModel createModel(final long id,
                                 final long creationTime) {
        final TreeModel treeModel = new TreeModel(id, creationTime);

        final List<Integer> roots = JooqUtil.contextResult(explorerDbConnProvider, context -> context
                .select(p.ANCESTOR)
                .from(p)
                .where(p.DEPTH.eq(0))
                .andNotExists(
                        context
                                .select(DSL.val("x"))
                                .from(p2)
                                .where(p2.DESCENDANT.eq(p.DESCENDANT))
                                .and(p2.DEPTH.gt(0))
                )
                .fetch(p.ANCESTOR));
        if (roots.size() > 0) {
            final Map<Integer, ExplorerNode> nodeMap = JooqUtil.contextResult(explorerDbConnProvider,
                            context -> context
                                    .selectFrom(n)
                                    .fetch())
                    .stream()
                    .collect(Collectors.toMap(ExplorerNodeRecord::getId, rec ->
                            ExplorerNode.builder()
                                    .type(rec.getType())
                                    .uuid(rec.getUuid())
                                    .name(rec.getName())
                                    .tags(NodeTagSerialiser.deserialise(rec.getTags()))
                                    .addNodeFlag(ExplorerFlags.getStandardFlagByDocType(rec.getType())
                                            .orElse(null))
                                    .build()));

            // Add the roots.
            roots.forEach(rootId -> {
                final ExplorerNode root = nodeMap.get(rootId);
                if (root != null) {
                    // The nodes as pulled from the DB don't have the rootNodeUuid set so any equality
                    // test on the system or favourite nodes will fail. Thus use the ones from constants
                    // so, we are comparing like with like.
                    if (Objects.equals(ExplorerConstants.SYSTEM_NODE.getType(), root.getType())) {
                        treeModel.add(null, ExplorerConstants.SYSTEM_NODE);
                    } else if (Objects.equals(ExplorerConstants.FAVOURITES_NODE.getType(), root.getType())) {
                        treeModel.add(null, ExplorerConstants.FAVOURITES_NODE);
                    } else {
                        treeModel.add(null, root);
                    }
                }
            });

            // Add parents and children.
            JooqUtil.contextResult(explorerDbConnProvider, context -> context
                            .select(p.ANCESTOR, p.DESCENDANT)
                            .from(p)
                            .where(p.DEPTH.eq(1))
                            .fetch())
                    .forEach(r -> {
                        final int ancestorId = r.value1();
                        final int descendantId = r.value2();
                        final ExplorerNode ancestor = nodeMap.get(ancestorId);
                        final ExplorerNode descendant = nodeMap.get(descendantId);
                        if (ancestor != null && descendant != null) {
                            treeModel.add(ancestor, descendant);
                        }
                    });
        }

        return treeModel;
    }
Get the root node containing a particular node, or the node itself if it is a root node.
stroom-explorer/stroom-explorer-impl-db/src/main/java/stroom/explorer/impl/db/ExplorerTreeDaoImpl.java