Return a column in hql select clause without actually using that column

Question

I have a query which returns a list of objects like such:

select distinct ref from references where [...]

I'd like to order these by a field not on ref but on an association of ref, i.e.:

select distinct ref from references where [...] order by ref.foo.name

Unfortunately this doesn't work in Oracle (once the query is translated to SQL) since it can only sort on this column if it's included in the select. Is there any way I can return the column as well, without hibernate actually picking it up? I'd like to just return a List instead of having to muck about with the Object[] arrays it would otherwise return.

Answer 1

It is perfectly possible for Oracle to sort a query by a column which is not included in the selection:

SQL> select ename from emp
  2  order by empno
  3  /

ENAME
----------
CLARKE
VAN WIJK
PADFIELD
ROBERTSON
BILLINGTON
SPENCER
BOEHMER
RIGBY
SCHNEIDER
CAVE
KULASH
HALL
GASPAROTTO
KISHORE

14 rows selected.

SQL>

But as your ORDER BY clause looks a bit peculiar - order by ref.foo.name - perhaps I am missing some subtlety?

Edit Vincent pointed out the subtlety I was missing - the use of DISTINCT.

SQL> select distinct ename from emp
  2  order by empno
  3  /
order by empno
         *
ERROR at line 2:
ORA-01791: not a SELECTed expression


SQL>

The need to use DISTINCT is often indicative of a design flaw, either a missing element in the WHERE clause or an insufficiently normalised data model. Although this does not help the questioner who probably just needs to get a workaround.

Answer 2

On the Hibernate side, a simple solution would be that your DAO code takes care of your problem. Your request would add the column.

Your Dao method would receive from Hibernate a List, and translate it.

List<Object[]> rawResults = query....;
List<References> results = new ArrayList<References>(rawResults.size());
for(Object[] rawResult : rawResults) {
  results.add(rawResults[0]);
}
return results;