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

[Josh Moore (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4030?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33562#action_33562 ] 

Josh Moore commented on HHH-4030:
---------------------------------

If it's of interest: http://developer.postgresql.org/pgdocs/postgres/queries-with.html

> 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