Distinct, order by and Hibernate HQL

Recently someone posted a question on stackoverflow explaining that they were having a problem with a Hibernate HQL query that involved both order by and a distinct in the select. The query was across five linked entities – a many to many association, mapped by an entity called PreferenceDateETL, which links the Preference and DateETL entities, and then two properties on the join entity – Employee and Corporation. The person asking the question explained that they wanted to write a query like the following:

select distinct pd.preference 
from PreferenceDateETL pd 
    where pd.corporation.id=:corporationId 
    and pd.preference.employee.deleted=false 
    and pd.deleted=false 
    and pd.preference.deleted=false 
    and pd.dateETL.localDate>=:startDM 
    and pd.dateETL.localDate<=:endDM 
    and pd.preference.approvalStatus!=:approvalStatus 
order by pd.preference.dateCreated


However, when trying to write this, he got an exception saying that the date created is not in the select list. What is going on here and how can it be fixed? Well, the order by clause is always evaluated last in a database query, so you can only order on columns you have selected. Although it appears as though the above query will have all of the columns, in fact, Hibernate ends up having two instances of the preference table in its query, and the order by clause refers to a different one to the one in the select, hence the exception. The generated SQL has the form:

select
        distinct preference1_.id as id1_76_,
        ....things...
    from
        preference_date_etl preference0_ 
    inner join
        preference preference1_ 
            on preference0_.preference_id=preference1_.id cross 
    join
        preference preference2_ cross 
    join
        employee employee3_ cross 
    join
        date_etl dateetl5_ 
    where
        ...things... 
    order by
        preference2_.date_created


The problem with this query is really that using distinct is unnecessary. It is required in the above form of the query, because the query has been written to pick out instances of the many to many entity that we are interested in, then from those, navigate to the preference objects they link to. By definition, because the many to many entities are representing a many to many relationship, this can give you a list that includes duplicates. You can avoid this by restructuring the query so that the query on the many to many entities becomes a subquery. i.e. you pick out the many to many entities you are interested in, and then say "now get me all of the preference objects that are in the list of preference objects referred to by the PreferenceDateETL objects". This query doesn't have any duplicates, hence you do not need the distinct, and the order by clause will work correctly. Here is the HQL:

from Preference p where p in 
  (select pd.preference from PreferenceDateETL pd
  where pd.corporation.id=:corporationId
  and pd.deleted=false
  and pd.dateETL.localDate>=:startDM
  and pd.dateETL.localDate<=:endDM)
and p.employee.deleted=false
and p.deleted=false
and p.approvalStatus != :approvalStatus
order by p.dateCreated


If you want to read the original question, it is here:

http://stackoverflow.com/questions/25545569/hibernate-using-multiple-joins-on-same-table

This article is part of a series of articles on Hibernate. Others in the series include:

Hibernate query limitations and correlated sub-queries
Bespoke join conditions with Hibernate JoinFormula
Inheritance and polymorphism
One-to-many associations
One-to-one associations
Many-to-many associations