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

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?