[hibernate-issues] [Hibernate-JIRA] Created: (HHH-4030) Implement support for native recursive query functionality of popular DBMSes

[David Cracauer (JIRA)]

Implement support for native recursive query functionality of popular DBMSes
----------------------------------------------------------------------------

                 Key: HHH-4030
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4030
             Project: Hibernate Core
          Issue Type: New Feature
          Components: core, query-hql, query-sql
         Environment: Hibernate 3.3.2, MSSQL
            Reporter: David Cracauer


We have a couple of areas in our system where need to load tree structures from our database.  These trees can be very deep (often 20+ levels).  SQL Server 2005 introduced Common Table Expressions, a kind of in-line view that can be used recursively.  

These allow us to quickly get a result set like this:

id, label, parentId
1, foo, null
2, foo2, 1
3, foo3, 2
4, foo4, 2
5, foo5, 4
6, foo6, 1

>From a tree like this:

   [1, foo]
     |
     |  -[2, foo2]
     |        | - [3, foo3]
     |        | - [4, foo4]
     |              | - [5, foo5]
     |-  [6, foo6]

using a query like this (not tested): 

with MyCTE(
   id, 
   label,
   parentId)
as 
  ( select n.id, n.label, n.parentid
      from Node n
UNION ALL
  select c.id, c.label, c.parentId
from MyCTE c
    inner join Node n on n.id=c.parentId)
select * from MyCTE

It happens many times more quickly than we've been able to load the graphs with Hibernate, even using batching etc.

We've tried using hibernate's built in native sql support without success.  The alias injection breaks the Common Table Expression definition ( with MyCTE ), as it is a view definition and doesn't allow for the 'id as id_276' syntax, rather requiring just a column name.

I know that there are several other major DBMSes that support recursive querying now.  Is there a way to have support for this functionality in hibernate core?


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira