Hibernate ‘SELECT DISTINCT’ Question

Here’s an interesting problem for you Hibernate freaks out there. I have two classes: Order and OrderItem where OrderItem has a property ‘orderid’. I need to write a query that returns all the distinct orders that match criteria ‘b’, where ‘b’ could be anything from the date time created, to the order amount to the SKU on the OrderItem AND I need to be able to order the results by any of the columns on the Order class. So I’ve ended up with a Hibernate HQL query that looks something like this:

Query q = null;
Session session = HibernateFactory.currentSession();
String sql = "SELECT DISTINCT o " +
  "FROM com.mycompany.Order AS o, com.mycompany.OrderItem as i " +
  "WHERE   o.id = i.orderid AND " +
  "o.datetimecreated >= :date1 AND " +
  "o.datetimecreated
The problem is that it appears that Hibernate takes my query and turns it into this:

SELECT distinct o.id
FROM myordertable as o, myitemstable as i
WHERE ...
ORDER by o.datetimecreated

but this isn't valid SQL, or at least it's not valid SQL for SQL Server, which returns the message:

Order by items must appear in the select list if SELECT DISTINCT is specified

The workaround is semi-trivial; I put all the columns that might show up in the order by clause in the select list and then instead of iterating over the returned Order objects, I iterate using the row of objects that is created, retrieve the order ID, fetch the order object using the ID and then add the resulting order to my collection:

List list = q.list();
for (int i=0; i
Is there is another way that I'm missing?

This entry was posted in J2EE. Bookmark the permalink.

4 Responses to Hibernate ‘SELECT DISTINCT’ Question

  1. Gavin says:

    This behavior is probably a misfeature, and we have an improvement planned for Hibernate3, but not implemented yet.

  2. Leo Palacios Garcia says:

    I had the same requirement and solved it this way:

    Query q = null;
    Session session = HibernateFactory.currentSession();
    String sql = “SELECT DISTINCT o, o.datetimecreated ” +
    “FROM com.mycompany.Order AS o, com.mycompany.OrderItem as i ” +
    “WHERE o.id = i.orderid AND ” +
    “o.datetimecreated >= :date1 AND ” +
    “o.datetimecreated <= :date2 AND ” +
    “o.amount < 10.00 ” +
    ” … ” +
    “ORDER by o.datetimecreated”;
    q = session.createQuery(sql);

    Vector data = new Vector();
    Iterator itData = null;

    itData = query.iterate();
    while(itData.hasNext()){
    data.add((AbstractDataBean)((Object[])itData.next())[0]);
    }

  3. Ashish says:

    Thanks!!!
    This is a good Example for distinct keyword and
    my problem has solved

  4. Mac says:

    Perfect!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>