Hibernate generates cross join instead of left join

Question

I'm using hibernate 3.6.10.Final and MySQL 5.5.

I wrote a dynamic query like this:

"from " + clazz.getName()+ " ORDER BY "+sortField+" "+sortDirection

My hibernate entities have a many to one parent/child relationship. if the query is ordered by a field from parent, hibernate generates this hql select:

select parent0_.ID as ID10_, parent0_.ID_CHILD as ID6_10_ 
from parent parent0_
order by parent0_.PARENTFIELD ASC

if the query is ordered by a field from child, i have the following hql:

select parent0_.ID as ID10_, parent0_.ID_CHILD as ID6_10_
from parent parent0_
cross join child1_
where parent0_.ID_CHILD = child1_.ID
order by child1_.CHILDFIELD ASC

The second query returns less results because parent0_.ID_CHILD can be null. Is there a way to force hibernate to generate a left join?

I need something like this

select parent0_.ID as ID10_,
   parent0_.ID_CHILD as ID6_10_
from
parent parent0_
    left join
child child1_
on
parent0_.ID_CHILD = child1_.ID
order by child1_.CHILDFIELD ASC

Is that an answer? I don't want to write a particular query for my parent and child entities. I want to reuse that generic query forcing hibernate to take parents with null child too. — Tostis, Mar 10 '13 at 09:56
Then you need a query with an explicit left join. There's no way around it. `child.parent.someProperty` will always generate an inner join. Read the documentation. It's in there. — JB Nizet, Mar 10 '13 at 09:59

score 2 · Accepted Answer · answered Mar 13 '13 at 08:48

Thanks to JB Nizet i solved writing the query like this:

String[] tokens = sortField.split("\\.");
for(int i=0;i<tokens.length-1;i++)
{
    sortby+=" LEFT JOIN parent"+i+"."+tokens[i]+" as child"+i;
}
sortby+=" ORDER BY child"+(tokens.length-2)+"."+tokens[tokens.length-1]+" "+sortDirection;
...
String query = "select parent0 FROM " + clazz.getName()+" as parent0 "+ sortby;

Hibernate generates cross join instead of left join

1 Answers1