Order By Child Object Property In Query

ChrisW1229 · **Joined:** Fri May 28, 2004 3:07 pm **Posts:** 4

Hello all,

I am new to Hibernate and have been trying for quite some time to execute a query that is ordered by a property on the child of an object.

A simplified version of my mapping files include:

Code:

<class name="Document" table="DOC">
   <id name="id" type="java.lang.Long" column="DOC_ID">
         <generator class="sequence">
            <param name="sequence">DOC_SEQ</param>
         </generator>
   </id>
   <property name="title" column="TITLE" type="string" />
   <many-to-one name="classification" class="Classification" column="CLASS_ID" />
</class>

<class name="Classification" table="CLASSIFICATION">
   <property name="name" column="NAME" type="string" />
   <property name="order" column="ORDER_ID" type="java.lang.Integer" />
   <property name="active" column="ACTIVE" type="java.lang.Boolean" />
</class>

This query works fine for me:

Code:

select distinct from Document as doc where doc.id > 1000 order by lower(doc.name) asc

I then use those results to print out a list of documents with their classifications in a table.

Now I want to also add something like the following, to sort by classification name as well:

Code:

select distinct doc from Document as doc where doc.id > 1000 order by lower(doc.classification.name) asc

This returns no results though, so I assume I am not doing something right. I looked in the Hibernate docs and saw some query examples doing something similar with joins so I also tried:

Code:

select distinct from Document as doc join doc.classification as cl where doc.id > 1000 order by lower(cl.name) asc

This also gives me no results, so I was wondering if someone could give some help?

I am using Hibernate 2.0 and Oracle 9i if that makes any difference.

Thanks,
Chris

christian · **Posted:** Fri May 28, 2004 3:42 pm

That should work. Enable the SQL log (show_sql to true) and see whats executed. "No result" certainly looks fishy.

michael · **Posted:** Fri May 28, 2004 3:42 pm

The best way would probably be to take a look at the generated SQL.

ChrisW1229 · **Joined:** Fri May 28, 2004 3:07 pm **Posts:** 4

As I mentioned before I simplified this example because i have many attributes, so the 'LotImpl' below is actually the Document class I described earlier.

I wrote a very small stand alone test outside of my application and I get the following SQL output:

Code:

select distinct lotimpl0_.DOC_ID as DOC_ID, lotimpl0_.TITLE as TITLE, lotimpl0_.CLASS_ID as CLASS_ID,
from LOT lotimpl0_ inner join CLASSIFICATION classifi1_ on lotimpl0_.CLASS_ID=classifi1_.CLASS_ID where (lotimpl0_.DOC_ID>1000 ) order by LOWER(classifi1_.NAME)ASC

After making the stand alone application I also am getting an exception now, which is strange because I had the original code set to print out any stack traces as well, but it didn't show up for some reason. I tried this same SQL in Toad and I get the same error as below:

Code:

May 28, 2004 3:48:45 PM net.sf.hibernate.util.JDBCExceptionReporter logException
s
WARNING: SQL Error: 1791, SQLState: 42000
May 28, 2004 3:48:45 PM net.sf.hibernate.util.JDBCExceptionReporter logException
s
SEVERE: ORA-01791: not a SELECTed expression

May 28, 2004 3:48:45 PM net.sf.hibernate.JDBCException <init>
SEVERE: Could not execute query
java.sql.SQLException: ORA-01791: not a SELECTed expression

   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
   at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
   at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
   at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
   at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
   at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java
:2672)
   at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedState
ment.java:589)
   at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatem
ent.java:527)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:71)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:551)
   at net.sf.hibernate.loader.Loader.doFind(Loader.java:140)
   at net.sf.hibernate.loader.Loader.find(Loader.java:620)
   at net.sf.hibernate.hql.QueryTranslator.find(QueryTranslator.java:928)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1343)
   at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:76)
   at Hibernate.main(Hibernate.java:52)
java.sql.SQLException: ORA-01791: not a SELECTed expression
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
   at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
   at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
   at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
   at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
   at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java
:2672)
   at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedState
ment.java:589)
   at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatem
ent.java:527)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:71)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:551)
   at net.sf.hibernate.loader.Loader.doFind(Loader.java:140)
   at net.sf.hibernate.loader.Loader.find(Loader.java:620)
   at net.sf.hibernate.hql.QueryTranslator.find(QueryTranslator.java:928)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1343)
   at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:76)
   at Hibernate.main(Hibernate.java:52)
rethrown as net.sf.hibernate.JDBCException: Could not execute query: ORA-01791: 
not a SELECTed expression
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1346)
   at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:76)
   at Hibernate.main(Hibernate.java:52)
Caused by: java.sql.SQLException: ORA-01791: not a SELECTed expression
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
   at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
   at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
   at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
   at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
   at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java
:2672)
   at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedState
ment.java:589)
   at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatem
ent.java:527)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:71)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:551)
   at net.sf.hibernate.loader.Loader.doFind(Loader.java:140)
   at net.sf.hibernate.loader.Loader.find(Loader.java:620)
   at net.sf.hibernate.hql.QueryTranslator.find(QueryTranslator.java:928)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1343)
   ... 2 more

ChrisW1229 · **Joined:** Fri May 28, 2004 3:07 pm **Posts:** 4

A little more information:

If I add "classifi1_.NAME as NAME" to the selected values in SQL, then I get the correct results in Toad without SQL errors. Doing this in HQL would essentially look like this though:

Code:

select distinct doc, cl from Documentl as doc join doc.classification as cl where doc.id > 1400 order by lower(cl.name) asc

If I do that however, then I can't just grab my Document objects out of the List in the user interface and call getClassification().getName() to fill my table like I was before, since this of course causes a ClassCastException.

michael · **Posted:** Fri May 28, 2004 5:52 pm

This rather sounds like a database limitation to me, it looks like it is not possible to order by columns not in the select clause. So what you want to do is not possible most likely.

ChrisW1229 · **Joined:** Fri May 28, 2004 3:07 pm **Posts:** 4

I was beginning to think that myself...I guess I can try sorting them using Java code instead.

Thanks for your help.

codeword · **Joined:** Fri Aug 05, 2005 3:50 pm **Posts:** 2

I have encountered the same problem running off an oracle database. I have played around with the query directly a SQL client and have come to the conclusion that you cannot order by fields that are not in the select statment IF you are using distinct in the select clause

e.g

Code:

select
distinct
    NICKNAME
from 
    PEOPLE
order by
    LASTNAME,
    FIRSTNAME

won't work

Code:

select
    NICKNAME
from 
    PEOPLE
order by
    LASTNAME,
    FIRSTNAME

will work but could result in duplicate entries

Code:

select
distinct
    LASTNAME,
    FIRSTNAME,
    NICKNAME
from 
    PEOPLE
order by
    LASTNAME,
    FIRSTNAME

will work but you have to add the fields being ordered by in the select clause.

All of this also applies to any fields in joined tables as well.

The problem with using hibernate and joined tables is that hibernate wants to do lazy loading and use proxied objects. This means that in it's initial select, it will want to select the data from the parent object/table ONLY, and it will want to ensure that it gets a uniqe list (using distinct) this means that in HQL if you are joining any tables you cannot order by any of the child objects.

The problem in doing it the ordering at the Java level is that it then forces me to pull all my data initially, and I loose the benefits of paging, as well as the lazy loading.

Does this make sence to anyone? Please someone tell me I am wrong about this as I would like to find a way to do this sorting at the database level.

shmish111 · **Joined:** Fri Dec 17, 2010 7:05 am **Posts:** 5

Well this is a very old post so maybe there is a better way round this these days but in case anyone needs this... I am using Hibernate 3.2.6.

I worked out a very simple solution to this problem after a LONG time scouring Google. This requires using the AliasToBeanResultTransformer though so it's only good for certain situations. In my case I had search results which only needed id, gridRef and child.name. In this case, you can do it if you add a new variable and accessors to your parent class to hold the child variable.

Code:

createAlias(aliases, criteria, "child", "child");
criteria.addOrder(Order.asc("child.name").ignoreCase());
      
//Only need to SELECT a few things
criteria.setProjection(Projections.distinct(Projections.projectionList().add(Projections.property("id"),"id").add(Projections.property("child"),"child").[b]add(Projections.property("child.name"),"name")[/b].add(Projections.property("lastUpdateTime"),"lastUpdateTime"))).setResultTransformer(new AliasToBeanResultTransformer(Parent.class));
       
return criteria.list();

Incidentally, the problem seems to stem from the fact that you can't use an alias on a where clause. Do newer versions of hibernate avoid the use of this alias when using Projections.distinct?