Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.1

Name and version of the database you are using: 0racle 10

I am attempting to write queries against tree-structured data. Parent and child objects are of the same type (stored in the same table, and mapped through a getParents() collection. The parent-child relationship is many to many.

My need is to write an HQL query which returns a collection of these values which have all of a given set of parents, but possibly more parents than those in the set. So for a set



I need to write HQL that efficiently returns all values which have at least all those required parents.

Currently, I am generating dynamic HQL by iterating over the set of required parents and generating an in clause for each of them, e.g. for a two-required-parent case



with the two values from the required set passes as arguments. But this produces nasty repeated selects for the parents in the generated SQL, and seems wrong just on the esthetic level.

Is there a better way to do a collection-subset query like this?

I am assuming that parent to value is many-to-many, and that there is a mapped Java class representing this relation called ValueToParent, with two fields called "valueRef" and "parentRef". Then I would try doing something like this:



Bind :parent_ids to a collection of parent IDs. Bind :num_of_parents to the number of distinct values in that collection. The query should get you only the values with all parents from your query.

I am not 100% certain on how fast this thing is going to be (and even if HQL parser will take it), but there is a good chance that it might help.

Please give it a try and let me know if it helped :-)