Hi all,

I have a one-to-many relationship, which is normally lazely loaded.
For performance reasons, Somewhere I'd like to retrieve the result in a single query, but sorted on a parent field, and then, per parent, sorted on a child field.
But somehow, the criteria api always sorts on the child field first, and then returns the parents in the wrong order.
It should be the opposite: sort on parent field first, and then on the child field.

QUESTION:
Is there any way I can influence the order in which sort fields appear in a criteria query for a one-to-many (parent-child), LEFT or OUTER joined relationship fetched eagerly?

DETAILS:
Both entities have a seqNo (int) sortfield:
Parent: Status (sorted by seqNo)
Child: StatusDetail (also sorted by seqNo)

Here's the XDoclet definition of the relation in the parent (Status):

/**
* @hibernate.set table="statusDetail" lazy="true" order-by="seqNo" cascade="all-delete-orphan"
* @hibernate.key column="statusId"
* @hibernate.one-to-many class="com.company.StatusDetail"
*/
public Set<StatusDetail> getStatusDetails() {
return statusDetails;
}

Here's the criteria:

Criteria criteria = session.createCriteria(Status.class).
addOrder(Order.asc("seqNo")).
add(Restrictions.eq("other Restrictions", ... )).
setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

Criteria subCriteria =
criteria.createCriteria("statusDetails", CriteriaSpecification.LEFT_JOIN);

List<Status> list = criteria.list();

It turns out that the query I need (handwritten) is something like:

select status.id,
status.name,
status.seqNo,
detail.statusId,
detail.id,
detail.statusId,
detail.seqNo
from status
left join statusDetail detail on status.id=detail.statusId
where status.statusType = 1
order by status.seqNo asc, detail.SeqNo asc

And the query resulting from the criteria always switches the sort order: The result is first sorted on the *detail.seqNo* and then on the *status.seqNo*.

The sql from the criteria ends with:

order by statusdeta1_.seqNo, this_.seqNo asc

where this_ is the status table alias, and statusdeta1_ is the detail alias.

Any idea's?
TIA
Ronald