Returning a column in a hql select statement without actually using that column
I have a query that returns a list of objects like this:
select distinct ref from references where [...]
I would like to order them by field, not by reference, but in the ref association, i.e .:
select distinct ref from references where [...] order by ref.foo.name
Unfortunately this doesn't work in Oracle (as soon as the query is translated to SQL) as it can only sort that column if included in the select. Is there a way to get the column back, without hibernation, to actually collect it? I would like to just return a list instead of guessing with the Object [] arrays it would otherwise return.
source to share
It is possible that Oracle can sort the query by a column that 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 since your ORDER BY clause looks a little odd order by ref.foo.name
- perhaps I am missing some subtlety?
Edit
Vincent pointed out a subtlety I was missing - using 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 for use DISTINCT
often indicates a design flaw, either a missing element in the proposal WHERE
, or an insufficiently normalized data model. While that doesn't help the interrogator, who probably just needs a workaround.
source to share
On the Hibernate side, a simple solution would be that your DAO code takes care of your problem. Your query will add a column.
Your Dao method will get 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;
source to share
In Oracle, when you use the "aggregation" function (single, group by, count, ...) to sort the results, you need to include the desired field in the select clause, because the "single" clause just works like a "group" then, what hibernation does is select all columns in your object with "perfect" aggregation. Perhaps you should try to use the "group by" function in HQL so that hibernate can translate your connection into the function group in the correct form.
source to share