"Elements" & "Order by" clause

mopt · **Posted:** Mon Feb 02, 2004 6:18 am

Hello all,

Here's my question :

I have this mapping :

Code:

<hibernate-mapping>
   <class name="StatistiquePersistent" table="STATISTIQUE">
      <id name="numStatistique" column="PK_STATISTIQUE">
         <generator class="sequence">
            <param name="sequence">S_STATISTIQUE_ENR</param>
         </generator>
      </id>
      <property name="entiteId" column="ENTITE_ID" not-null="true"/>
      <many-to-one name="typeStat" column="FK_TYPE_STATISTIQUE" not-null="true" class="TypeStatistiquePersistent"/>
      <set name="statEnregistrements" lazy="true" inverse="true" cascade="save-update">
         <key column="FK_STATISTIQUE"/>
         <one-to-many class="StatistiqueEnregistrementPersistent"/>
      </set>
   </class>
</hibernate-mapping>

<hibernate-mapping>
   <class name="StatistiqueEnregistrementPersistent" table="STATISTIQUE_ENREGISTREMENT">
      <id name="numStatistiqueEnregistrement" column="PK_STATISTIQUE_ENR">
         <generator class="sequence">
            <param name="sequence">S_STATISTIQUE_ENR</param>
         </generator>
      </id>
      <property name="cumul" column="CUMUL" not-null="true"/>
      <many-to-one name="etat" column="FK_ETAT" class="EtatPersistent"/>
      <property name="ordreAffichage" column="ORDRE_AFFICHAGE" not-null="true"/>
      <many-to-one name="statistique" column="FK_STATISTIQUE" class="StatistiquePersistent"/>
   </class>
</hibernate-mapping>

I need to load my "Statistiques" and its set of "StatistiquesEnregistrement" ordered by their "ordreAffichage" property.
My query was :

Code:

queryString = "select listeStat from StatistiquePersistent as listeStat " +
                     "and listeStat.typeStat.libelleTypeStatistique <> :typeStatJournalier " +
                           "group by listeStat.typeStat.numTypeStatistique " +
                           "order by listeStat.typeStat.numTypeStatistique asc, " +
                           "listeStat.statEnregistrements.ordreAffichage asc" ;

And then I had this exception :

Code:

expecting 'elements' or 'indices' after: ordreAffichage

I don't see how to use "elements" with an "order by" clause.
Have someone an hint for me ?

Thanx in advance.

emmanuel · **Posted:** Mon Feb 02, 2004 6:53 am

where is you where clause.
And I don't think your request can be achieve even on SQL. You grouped by a value and want to order by a non related one. Try do do the SQL one first and do the HQL step by step

mopt · **Posted:** Mon Feb 02, 2004 7:02 am

Sorry, I mistaped the query :

Code:

queryString = "select listeStat from StatistiquePersistent as listeStat " + 
                     "where listeStat.typeStat.libelleTypeStatistique <> :typeStatJournalier " + 
                           "group by listeStat.typeStat.numTypeStatistique " + 
                           "order by listeStat.typeStat.numTypeStatistique asc, " + 
                           "listeStat.statEnregistrements.ordreAffichage asc" ;

The pb is that in SQL I don't use "elements", so, I don't know how to write my query in SQL :/

emmanuel · **Posted:** Mon Feb 02, 2004 8:37 am

In SQL, you can only select columns that are part of your group by and you can order by the columns that are part of you select. HQL will have such limitations.

My understanding of you problem let me think you break that.