How to order by optional attributes without restricting?

TRex2003 · **Joined:** Thu Sep 03, 2009 12:37 pm **Posts:** 13

I like to order by optional attributes in a child relation:

Object A has a one-to-many relationship to Object B. Some A's have a relevant B (B.isRelevant, for example), some others don't. I like to sort them by B.name, but the following HQL removes all A without a relevant B:

Code:

from A join A.B b
  where B.isRelevant=true
  order by B.name

I want to have all A (just like "from A").

Can this be done with HQL (or criteria)? Or do I really have to sort all the A's manually (urghs..) after fetching?

saltnlight5 · **Posted:** Thu Sep 09, 2010 5:05 pm

The default association is a INNER JOIN, but what you want is a LEFT JOIN, which will return your "optional" values.

try

Code:

from A left join A.B as b
  where b.isRelevant=true
  order by b.name

TRex2003 · **Joined:** Thu Sep 03, 2009 12:37 pm **Posts:** 13

I already tried this, but on the wrong part ;)

My case is a m:n relationship with A:N, N:M, M:B tables.I put the "left join" on the M:N object. Your posting encouraged me to try it again and I found the solution in putting the "left join" on the M:B relation ;)

Thank you.