{"id":620,"date":"2004-07-29T16:24:23","date_gmt":"2004-07-29T20:24:23","guid":{"rendered":"http:\/\/wordpress.cephas.net\/?p=620"},"modified":"2004-07-29T16:24:23","modified_gmt":"2004-07-29T20:24:23","slug":"hibernate-select-distinct-question","status":"publish","type":"post","link":"https:\/\/cephas.net\/blog\/2004\/07\/29\/hibernate-select-distinct-question\/","title":{"rendered":"Hibernate ‘SELECT DISTINCT’ Question"},"content":{"rendered":"

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<\/strong> 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:
\n
\nQuery q = null;
\nSession session = HibernateFactory.currentSession();
\nString sql = \"SELECT DISTINCT o \" +
\n  \"FROM com.mycompany.Order AS o, com.mycompany.OrderItem as i \" +
\n  \"WHERE   o.id = i.orderid AND \" +
\n  \"o.datetimecreated >= :date1 AND \" +
\n  \"o.datetimecreated
\nThe problem is that it appears that Hibernate takes my query and turns it into this:
\n
\nSELECT distinct o.id
\nFROM myordertable as o, myitemstable as i
\nWHERE ...
\nORDER by o.datetimecreated
\n<\/code>
\nbut this isn't valid SQL, or at least it's not valid SQL for SQL Server, which returns the message:
\n
\nOrder by items must appear in the select list if SELECT DISTINCT is specified
\n<\/code>
\nThe 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:
\n
\nList list = q.list();
\nfor (int i=0; i
\nIs there is another way that I'm missing?<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Continue reading Hibernate ‘SELECT DISTINCT’ Question<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[],"_links":{"self":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts\/620"}],"collection":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/comments?post=620"}],"version-history":[{"count":0,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts\/620\/revisions"}],"wp:attachment":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/media?parent=620"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/categories?post=620"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/tags?post=620"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}