Trouble with hql order by

run4fun · **Joined:** Sat May 29, 2004 2:16 pm **Posts:** 81

i've the following statement:

Code:

 String query = "select elements(rel.turma.detalhe) from Relatorio as rel where rel.turma.ano = " + oano +  " AND turma = " + aturma;

This query returns several strings (corresponding to field estrategia in table detalhes), and i'd like to order them "asc".
But everything i try gives me sintax error or else doesnt show up at all.
I tried (without success):
order by elements asc
order by elements(rel.turma.detalhe) asc
order by rel.turma.detalhe asc
order by rel.turma.detalhes.estrategia - sql syntax error
and so on...
my mappings:
Detalhe

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
   <class name = "relatorio.Detalhe" table = "detalhes">
      <id name = "id" column = "id" unsaved-value = "null">
         <generator class = "increment" />
      </id>
      <property name = "estrategia" column = "estrategia" />
   </class>
</hibernate-mapping>

Detalhe is a child of Turma:
[code]<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name = "relatorio.Turma" table = "turmas">
<id name = "id" column = "id" unsaved-value = "null">
<generator class = "increment" />
</id>
<property name = "turma" column = "turma" />
<property name = "ano" column = "ano" />
<property name = "masculinos" column = "masculinos" />
<property name = "femininos" column = "femininos" />
<!--
retirei lazy = true para poder fechar a sess

Brannor McThife · **Posted:** Thu Jun 10, 2004 11:47 am

What database are you using?

Here's an example of one of mine:

Code:

            StringBuffer sb = new StringBuffer();
            sb.append("SELECT dv ");
            sb.append("FROM " + DVersion.class.getName() + " as dv");
            sb.append(", " + DSuiteT.class.getName() + " as ds ");
            sb.append("WHERE ds.versionGroup = dv.versionGroup ");
            sb.append("AND ds.suiteID = ");
            sb.append(header.getSuite());
            sb.append(" AND dv.liveDate <= '");
            sb.append(dateTodayYMD());
            sb.append("' ");
            sb.append("GROUP BY dv.versionID order by dv.id desc");

And that works fine with Hibernate and MYSQL (also works with MS SQL 2000).

-G

run4fun · **Joined:** Sat May 29, 2004 2:16 pm **Posts:** 81

my database is mysql too
i guess problem comes from elements being a special function, so the query works on those instead of each database item

fabreax · **Posted:** Fri Apr 27, 2007 10:23 am

Hello, I'm french so sorry for my English,

Did you solve your problem ?

I have 2 persistant classes :

Entite which contains a List of Document called documents
and Document which contains several properties.

I want to get the Entitie documents and ORDER BY the documents date.

My wrong HQL query is :

Quote:

SELECT elements(e.documents) FROM Entite e WHERE e.id="+idEntitie+" ORDER BY elements(e.documents).date DESC"

But it's wrong :S

Thank you

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

I think it's because you are using Sets which by their very nature are unordered, I belive if you want to order them you need your data obejcts (Domain obecjts) to impliment hash code.
It might be posible to generate your hibernate templates so that results can be fetched into lists instead of Sets.

fabreax · **Posted:** Fri Apr 27, 2007 1:19 pm

Thank your for your answer.

It's complicated :S I will use Native SQL instead ...