Edit: I solved the problem describe below using HQL and currently I am looking for answers to these questions:

1. Is there any way, how to apply extra join conditions using Criteria API? (The "with" keyword in HQL)
2. Is there any way, how to specify join order using parentheses in HQL / Criteria API?


I was not able to find this in the documentation or anywhere on the web and I wonder, why nobody has raised this question anywhere (though I found related problems on the forum, which have not been replied by anybody).

The problem is related to left outer join.

Simple scenario. Parent(id) and Child(id, age). There is a relation (1..n) called "children" between Parent and Child. Parent(A) has no children, Parent(B) has three Children aged 10, 20 and 30.



...generates...



I only get 2 results:

Parent(B)+Child(20)
Parent(B)+Child(30)

The result I need is what I need from a left outer join:

Parent(A)+null
Parent(B)+Child(20)
Parent(B)+Child(30)

... in other words, I need this generated SQL query:



My questions:
1. How can I achieve the needed result? Something, that would show me, that I missed some part of the Hibernate technology. A workaround would be nice too (a only came with adding a isNull() restriction to the criteria).


.... if question 1 has no solution in Hibernate/Criteria API/HQL

2. What is the point of adding restrictions to SubCriteria object, when they are always applied to the same where clause in the generated SQL. Is this only for the purpose of method chaining?

3. Does this mean, that the left outer join mode for fetching associations is useless once I apply a restriction to the resulting set ?

from the docu:


Try to use setFetchMode().

That is not the problem I have. Again, the problem is, that I need to apply a restriction to a different part of a query. Not in the global where clause, but the on part of the join clause to limit the results of a subresult before joining it with the root table. I originally thought, that this could be achieved by calling add(Restriction) on particular SubCriteria objects.

Since it makes no difference which Criteria object gets a restriction applied, I am afraid, this is not possible in Hibernate at all, but I would like to hear a confirmation from someone else.

Ok, I found how to supply extra join conditions in HQL. My bad :(



Now I will try to find a way, how to do it in Criteria API.... If anybody knows, before I post here again, please reply.

Did you checkout Section 15.4 of the reference docs

When working with outer joins (left in this case), there is a difference if you put restriction to the where part of the SQL query or to the join condition part.

The example you supplied would put the restriction on the kittens weigth to the where clause of the whole query, and eliminating all cats that have no kittens. Cats with no kittens (cats + null results of the left join) get eliminated by the restriction.

This can be solved by with keyword in the HQL query. Remaining questions are:
1. Is the any equivalent for "with" keyword in Criteria API?
2. Is there any way how to specify join order in the HQL? The default is (((A join B) join) C) join D. Is it possible with HQL or Criteria API to perform e.g: (A join B) join (C join D) ? I checked the HQL grammar and it seems that parentheses are not allowed.

I'll second that - would love to see something equivalent to hql "with" keyword in Criteria. Or there is something like it in Criteria already?

Hi!, Im facing the some problem that you had some time ago, did you come up with a solution for this eventually?

I can see no way to do this join with the criteria api, although with hql is so easy,

Any help would be very appreciate.

Cheers.

Any progress on this issue?
I have a great improovement of perfomance if i use extra conditions in outer join instead of where cause...
from 41 seconds to 110ms...

Is it possible now?

I just spent several hours trying to track this down, and at last landed on this thread. I'm sad to see there hasn't been any solution posted for almost 2 years - if there isn't a solution here on this forum, it most likely means that it's still not possible to do this using the Criteria API. :(

I found a JIRA issue that I believe is closely related to this:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2308

I think the only thing we can do now is vote for it, right?

Going through the comments posted under the JIRA listed above, Theo Platt came with a very nice workaround - actually the best workaround that I found so far, and works nicely for me. I'm posting it here for those in need, since I know how hard it was for me to find it.

The workaround is to apply a Hibernate filter on the collection - a filter allows you to put the condition/restriction on the JOIN part of the query rather than WHERE when applied to a collection (if you let hibernate display the SQL it generates, it will add this to the ON clause it generates for the join).

Here's what you need to do:

1. Create a filter using either the @FilterDef annotation or <filter-def> if you're using XML files.
2. Apply the filter to your collection (in the parent table) using @Filter annotation or <filter>
3. When executing your Criteria API based query, make sure you enable your filter and supply the required parameter

I had a hard time figuring out the right way to specify the filter's condition applied to my collection to get it working, so if anyone needs more help, just let me know.

Hi immutability,

Is the Hibernate filter working for left outer join using criteria api?

Thanks.

I do not know...
But about answer of question 1 of topicstarter -
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2308

in NHibernate we do not have the "with" predicate.
is there any other workarund?!

I am facing the same issue,

How to add filter to the first part of the query:



Sharon