Hi all,

I'm write a query that uses a recursive table structure. Users are doing a search for a particular item in the database and they do not know beforehand where it sits on a tree of items. They search by the attributes of the item and then I will return a list of trees (of items) that contain an item that matches the the attributes.

So the Java objects look like:

Item
-----
Attribute1
Attribute2
Collection<Item>

The tables look like

Item
-----
itemid
attribute1
attribute2

Item_item
------------
itemidmaster
itemidrelated

I've been thinking about how I can approach this problem using hibernate. I don't know of a way to do this kind of query using the HQL, (If you can think of a way, please let me know!) so I'm writing a custom function to return a table of ids that represent the 'roots' of all trees that contain a match, then using Hibernate to construct the object graph.

Is there a way I can do something like

SELECT * FROM table(schema.searchfunction(attribute list)) as search
INNER JOIN item i1 ON i1.itemid= search.itemid

and have Hibernate construct the resulting objects for me?