I recently upgraded from using Hibernate 2.1.8 to 3.0.4.

I have a class with a set that has an "order-by" attribute specified:



And I run something like the following HQL:

from Customer customer
left join fetch customer.customerContactSet ccs
where customer.relationship = 'test'
order by customer.type asc

Using 2.1.8 the generated SQL had an order by clause like this:

where it appeared to combine the order by specified in the HQL and the mapping file order-by attribute, placing the HQL order by first.

With 3.0.4, if I run the same HQL the order by specified in the HQL seems to be ignored and only the order-by specified by the mapping file is used:


This continues to be the case in any HQL so long as I am making a left join fetch to customerContactSet.

Thanks in advance,
Jared



Hibernate version:
3.0.4

Code between sessionFactory.openSession() and session.close():
String query = "from Customer customer
left join fetch customer.customerContactSet ccs
where customer.relationship = 'test'
order by customer.type asc";
List list = s.createQuery(query).list();

Name and version of the database you are using:
Oracle 10g

The generated SQL (show_sql=true):
see above

I want to add a second part to this issue. Using the HQL example above:

from Customer customer
left join fetch customer.customerContactSet ccs
where customer.relationship = 'test'
order by customer.type asc

is there any way to order not just customers but the elements in customer's customerContactSet collection through a single HQL statement? As I understand it there is not, the only ways are mentioned in this post:

http://forum.hibernate.org/viewtopic.php?t=937384

1) An order-by attribute set in customerContactSet's mapping, which as I've noted above is conflicting with the order by in my HQL. Also I ideally I would like to be able to order customerContactSet by any of its properties, not just one.

2) Sorting the collection in memory

3) Sorting the collection using setFilter()

I just want to verify that these are the only options, that there isn't a way purely through HQL to order a collection's elements by any property when that collection is first initialized.

Thanks again,
Jared