For my company's current project, I have written several queries using HQL and it works great. I have tried to move to using the Criteria and related classes, because it seems like a more elegant way to go. Most things have been easy to go from HQL to Criteria. However, I have come across a difficult problem.

I have two tables COMPUTER and MANUFACTURER. COMPUTER references MANUFACTURER (in the DB.) In the mapping files, I have the relationship only defined from COMPUTER, i.e. :

<many-to-one name="manufacturer" class="Manufacturer" not-null="true">
<column name="MANUFACTURER_ID"/>
</many-tone>

But I don't have a corresponding <set> mapping in the mapping file for Manufacturer. The query I need to do is to retrieve all the computers of a certain type (all laptops for example) but sorted by a field on the manufacturer table/object.

The SQL is easy to do: select computer_id, computer_name, manufacturer_name, manufacturer_scode
from computer c, manufacturer m where
c.manufacturer_id =m.manufacturer_id sort by manufacturer_scode

This is only for illustrative purposes of what I'm trying to do. I actually all the fields for each computer, and a couple of fields from the manufacturer table.

So my question is can this be done using a Criteria? Can it be done using HQL? I tried it as Criteria, but it did not work:

Criteria criteria = session.createCriteria(Computer.class);
criteria.add(Expression("type", new Integer(4));
criteria.addOrder(Order.asc("manufacturer.manufacturerScode");
criteria.setFetchMode("manufacturer", FetchMode.EAGER);
List results = criteria.list();


This created an error that complained that there was no property called "manufacturer.manufacturerScode" on the Computer class. Of course this is true, but there is a manufacturer property that is a Manufacturer class and that class has a property manufacturerScode. Obviously I'm doing something wrong, but can this even be done with a Criteria? Can it be done with HQL instead?

For what it's worth, I'm using Hibernate 2.1.4, Oracle 9i, Weblogic 8.1 SP2, JDK 1.4.2_04.

I did read through the many FAQs (wouldn't it be great to organize these into an interactive application...) and have searched the forums. If there is something I missed (and there probably is!) then I apologize for wasting anyone's time.

I've searched the docs for exactly the same issue, but didn't find anything. Sorting based on a property inside a many-to-one class relationship seems to be difficult. Comments, anyone?



Then I try to sort a list of AClass instances for bClass.Name, which fails. Best regards,

Christian

Hello
I have the same problem. Can anyone confirm if there is a way to do this using Criteria queries? Creating normal aliases does not seem to work, perhaps an "orderalias" is needed.
Regards
Trygve

I could get this to work with HQL:

String hql = "from Computer as comp where comp.type=4 "+
"order by comp.manufacturer.manufacturerScode";

This is very intuitive and works nicely. Unfortunately the analogous Criteria does not work. So I'm sticking with HQL once again!

I've spent a lot of time on this one, but the short story is that the Criteria API will not let you do a search by example based on association fields! The only fields usable in an Example query are fields in the base class. Does this help?

You might also want to check out the JIRA issue HB-132. This is a proposed patch for adding order-by support for assoications in a criteria query.

Chris....

i had asked a similar question about 6 months ago and it was recommened that i use HQL for this purpose, which works great. considering how busy the development team has been with the 3.0 release i don't think this would be a high priority issue since you can accomplish this with HQL.