Hibernate version: 3.1
Full stack trace of any exception that occurs:
Name and version of the database you are using: MySQL 5.0

I have a parent record PARENT, and a child record CHILD (many-to-one, lazy).

Imagine I wish to retrieve a list of ParentRecords and all their (ChildRecords (all child records loaded JOIN style, so 1 query) where there exists at least one child record where child.someProperty is true.

Right now, I can get all parent rows, but loading the child rows requires an additional SQL select to load the children that did not have.

I need to figure out how to force a second join to child and make sure they end up in the result list.

Is it possible to navigate the same association twice in a criteria query?

Thx,

-JFD

The answer to your question is No, you can't make more than one join to a table using the Criteria API. You'll get a duplicate association path error when you attempt to execute your query.

I managed to get around this by using subqueries. I have a contact association table in my system which holds references to every different type of contact which can be associated with an order (shipping company, billing company, sales rep, etc...).

I used Detached criteria to accomplish this.



Then I add my restrictions to my detached criteria object:


Finally, I add my detached criteria object as a subquery to my main criteria object:



I do this up to 4 different times for that one class (InsertionOrderContact.class).

Hope this helps. Good luck.