Hibernate: Sorting by a field from a linked table if foreign key is null

Question

Lets say I have two tables - "Cat" and "Cat owner" that are linked with many-to-one like this:

<class name="com.example.Cat" table="cat">
     ... 
     <many-to-one name="owner"
      class="com.example.CatOwner"
      column="owner_id" 
      not-null="false" insert="true" update="true" cascade="none" lazy="false"/>
</class>

Now I want to get a list of all cats sorted by cat owner names, like this:

session.createQuery("from Cat cat order by cat.owner.name");

The problem is if a cat doesn't have an owner (owner_id=NULL), that cat won't be selected with this query. How can I make cats without owners to be present in the result?

Thanks.

Answer 1

Hi,

In standard SQL it's a typical example of an OUTER JOIN, so the fast answer is trying to write the query like:

session.createQuery("select cat from Cat cat, CatOwner cato WHERE cato.id(+) = cat.ownerId order by cato.name NULLS LAST");

NOTE: I have not tested the posted code! NOTE2: I suppose an Oracle RDBMS and hibernate.properties properly configured.

Answer 2

Take a look at Hibernate docs, 14.3. Associations and joins. Your query should be something like:

 from Cat cat left join cat.owner owner order by owner.name