HQL recursion, how do I do this?

Question

I have a tree structure where each Node has a parent and a Set<Node> children. Each Node has a String title, and I want to make a query where I select Set<String> titles, being the title of this node and of all parent nodes. How do I write this query?

The query for a single title is this, but like I said, I'd like it expanded for the entire branch of parents.

SELECT node.title FROM Node node WHERE node.id = :id

Cheers

Nik

Bozho · Accepted Answer · 2010-03-21T11:41:14.273

14

You can't do recursive queries with HQL. See this. And as stated there it is not even standard SQL. You have two options:

write a vendor-specific recursive native SQL query

make multiple queries. For example:

// obtain the first node using your query
while (currentNode.parent != null) {
   Query q = //create the query
   q.setParameter("id", currentNode.getParentId());
   Node currentNode = (Node) q.getSingleResult();
   nodes.add(currentNode); // this is the Set
}

I'd definitely go for the 2nd option.

edited Mar 21 '10 at 11:41

answered Mar 21 '10 at 11:35

Bozho

588,226
146
1,060
1,140

I know SQL isn't recursive, I assumed that HQL was designed with overcoming this limitation in mind. – niklassaers Mar 21 '10 at 12:01
I'd go for Bozho's second solution too, if the tree is not too deep or complex. But say your tree is ten "queries" deep or you have many nodes, for which you want to get this full title, you might end up with a lot of queries and a performance problem. One way to solve that, depending on your situation, is to write the full title to the db on saving the record, thus saving time when retrieving. – asgerhallas Mar 21 '10 at 12:02
That is indeed my problem, the tree can be very deep, which is why I'd like Hibernate to take care of optimizing how to interact with the database. I was hoping for it know smart solutions and possibly lead to a database transition into one that can handle such issues. – niklassaers Mar 21 '10 at 15:05
1

I think the answer thus is: HQL has no recursion, use other optimization strategies. The one I'm going for is "materialized paths". Thank you very much for your help :-) – niklassaers Mar 21 '10 at 15:58
@Bozho is there any chance you could show an example query with this. I seem to be missing something and can't figure it out. Thanks – Code Junkie Mar 15 '13 at 02:38
2

Actually, [Common Table Expressions are part of the SQL:1999 standard](http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression), so there is a way to do recursive SQL, but it's not supported by MySQL and Hibernate/JPA don't yet support it either. – Brad Cupit Mar 29 '13 at 19:42

score 10 · Answer 2 · answered Nov 19 '10 at 10:44

While it isn't possible to write the recursive query you're asking for, it is possible to eager fetch the hierarchy with HQL; doing this would at least allow you to walk the tree in memory without hitting the database for each level.

select n from Node n
left join fetch n.Children

score 1 · Answer 3 · answered May 25 '20 at 07:30

I know this question is old, but as it was linked in a different question, I wanted to give an update on this, as Blaze-Persistence offers support for working with recursive CTEs on top of the JPA model.

Blaze-Persistence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. To model CTEs or recursive CTEs, which is what you need here, you first need to introduce a CTE entity that models the result type of the CTE.

@CTE
@Entity
public class NodeCTE {
  @Id Integer id;
}

A query for your example could look like the following

List<String> titles = criteriaBuilderFactory.create(entityManager, String.class)
  .withRecursive(NodeCTE.class)
    .from(Node.class, "n1")
    .bind("id").select("n1.id")
    .where("n1.id").eq(nodeId)
  .unionAll()
    .from(Node.class, "n2")
    .innerJoinOn(NodeCTE.class, "cte")
      .on("cte.id").eq("n2.parent.id")
    .end()
    .bind("id").select("n2.id")
  .end()
  .from(Node.class, "n")
  .select("n.title")
  .where("n.id").in()
    .from(NodeCTE.class, "c")
    .select("c.id")
  .end()
  .getResultList();

This renders to SQL looking like the following

WITH RECURSIVE NodeCTE(id) AS (
    SELECT n1.id
    FROM Node n1
    WHERE n1.parent_id = :id
  UNION ALL
    SELECT n2.id
    FROM Node n2
    INNER JOIN NodeCTE cte ON n2.parent_id = cte.id
)
SELECT n.title
FROM Node n
WHERE n.id IN (
  SELECT c.id
  FROM NodeCTE c
)

You can find out more about recursive CTEs in the documentation: https://persistence.blazebit.com/documentation/core/manual/en_US/index.html#recursive-ctes

HQL recursion, how do I do this?

3 Answers3

Linked