order by an outer joined criteria

georgef · **Joined:** Fri Jun 02, 2006 1:23 am **Posts:** 27

How do I order by an outer joined criteria?

I'm trying the following but seem to get "org.hibernate.QueryException: could not resolve property: child.prop"

Code:

Criteria crit = session.createCriteria(Parent.class);
crit.setFetchMode( "child", FetchMode.JOIN );
crit.addOrder(Order.desc( "child.prop" ));
return crit.list();

My mappings look something like this,

Code:

<class name="Child" table="CHILD">
    <id name="id" column="ID">
        <generator class="foreign">
            <param name="property">parent</param>
        </generator>
    </id>
    <property name="prop" column="PROPERTY" />
    <one-to-one name="parent" 
        class="test.Parent"
        constrained="true"/>
</class>

<class name="Parent" table="PARENT">
    <id name="id" type="long" column="ID">
        <generator class="native"/>
    </id>
    <one-to-one name="child" class="test.Child" cascade="all"/>
</class>

Using an alias works but it makes it an inner join. I do can this with HQL but would prefer to use a Criteria. Is this possible?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I'd use an alias for this. Why would inner vs. outer matter in this case, anyway? It's a one-to-one, so both join types will produce exactly the same results.

georgef · **Joined:** Fri Jun 02, 2006 1:23 am **Posts:** 27

Because one parent can either have zero or one child. With an inner join parents with a null child will not be returned.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Then you shouldn't be using one-to-one. Change that to many-to-one unique="true", which means "one-to-zero-or-one".

Now that I understand that, to do the outer join, use something like this:

Code:

Criteria crit = session.createCriteria(Parent.class).
  .setFetchMode( "child", FetchMode.JOIN )
  .createCriteria("child")
  .addOrder(Order.desc( "prop" ));
return crit.list();

In this code, the Criteria object that addOrder is called on is the once created by createCriteria("child"). I believe that doing it this way (rather than with createAlias) uses outer joining. You may even be able to drop the setFetchMode call, I'm not sure.

You may have to use createCriteria("child", "ch").addOrder(Order.desc("ch.prop")), but I don't think so.

georgef · **Joined:** Fri Jun 02, 2006 1:23 am **Posts:** 27

But in order to change it to a many-to-one I will have to introduce a foreign key in my Parent table right? Its preferable if this could be avoided.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Nope. many-to-one joins to the primary key of the other class unless you specify property-ref. It's exactly the same as one-to-one.

Have a look at the ref docs, sections 7.4.2, 7.5.2 and 23.4.5 for "officially endorsed" examples of this.

georgef · **Joined:** Fri Jun 02, 2006 1:23 am **Posts:** 27

In section 7.4.2, Person is the parent object and the many-to-one mapping does specify a column attribute.

Anyway what i am trying to achieve is a bidirectional one-to-zero-or-one association on a primary key.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

The column attrbiute in many-to-one refers to the column in the "parent" table that maps to the primary key of the "child" table. For pk-to-pk, the column will be the pk of the "parent" table.

Anyway, whether you use one-to-one or many-to-one unique="true" is unimportant, for the purposes of your original question. You should still be able to use addOrder on session.createCriteria(Parent.class).createCriteria("child") to accomplish what you need. If that doesn't work, I think that you'll have to resort to subqueries.