Ordering a collection acquired with HQL select elements(c)

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

Hey all. I have posted a couple of posts on my set ordering not working and digging around discovered this was as I was using Set instead of SortedSet, but I have since decided that I can use BAG and List for my purposes.

I am still not getting my ordering right. I understood that using <bag order-by ..> would mean the order by clause would be added to SQL statements. However, it appears that because I use lazy collections and then go and load the lists directly with HQL (for performance reasons) that this does not get applies (see java calls and sql logging output).

Therefore my question is how to apply ordering to my collection within HQL. My queries are all of the type select elements(collectionName), but I do not know how to apply ordering by a field in one of these collections.

You advice appreciated as ever,

ADC

Hibernate version:

2.1.4

Mapping documents:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping
    PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping schema="dbSchedules.dbo">

    <class name="com.qas.newmedia.intranet.iq.dto.schedules.Schedule" 
      table="tbl_Schedules">

        <id name="id" type="int" column="schedule_id" unsaved-value="0">
           <generator class="identity" />
       </id>
            
      <discriminator column="schedule_type" type="string" />
            
        <property name="owner" column="owner" not-null="false" />
      <property name="associates" column="associates" not-null="false" />
      <property name="status" column="status" not-null="false" />
      <property name="title" column="title" not-null="true" />
      <property name="summary" column="summary" not-null="false" />
      <property name="created" type="calendar" column="created" not-null="false" />
      <property name="lastUpdated" type="calendar" column="last_updated" not-null="false" /> 

      <bag name="milestones" inverse="true" lazy="true" order-by="milestone_id" cascade="all">
            <key column="schedule_id"/>
            <one-to-many class="com.qas.newmedia.intranet.iq.dto.schedules.ScheduleMilestone" />  
        </bag>

      <bag name="updates" inverse="true" order-by="update_id" lazy="true" cascade="all">
            <key column="schedule_id" />
            <one-to-many class="com.qas.newmedia.intranet.iq.dto.schedules.ScheduleUpdate"/> 
        </bag>
      
      <bag name="links" inverse="true" order-by="link_id" lazy="true" cascade="all">
            <key column="schedule_id"/>
            <one-to-many class="com.qas.newmedia.intranet.iq.dto.schedules.ScheduleLink"/>
        </bag>
      
      <!-- 
        - Product Schedule
        -->
      
      <subclass 
         name="com.qas.newmedia.intranet.iq.dto.schedules.ProductSchedule" 
         discriminator-value="Product">
         
         <property name="developmentSignoff" type="calendar" column="development_signoff" not-null="false" />
         <property name="developmentSignoffFormat" column="development_signoff_format" not-null="false" />
         <property name="qatSignoff" type="calendar" column="qat_signoff" not-null="false" />
         <property name="qatSignoffFormat" column="qat_signoff_format" not-null="false" />
         <property name="launch" type="calendar" column="launch" not-null="false" />
         <property name="launchFormat" column="launch_format" not-null="false" />
       </subclass>
      
      <!-- 
        - QA For Schedule 
        -->      
      
      <subclass 
         name="com.qas.newmedia.intranet.iq.dto.schedules.QAForSchedule" 
         discriminator-value="QAFor">
         
         <property name="developmentSignoff" type="calendar" column="development_signoff" not-null="false" />
         <property name="developmentSignoffFormat" column="development_signoff_format" not-null="false" />
         <property name="qatSignoff" type="calendar" column="qat_signoff" not-null="false" />
         <property name="qatSignoffFormat" column="qat_signoff_format" not-null="false" />
         <property name="launch" type="calendar" column="launch" not-null="false" />
         <property name="launchFormat" column="launch_format" not-null="false" />
       </subclass>            

      <!-- 
        - Data Acquisition Schedule 
        -->

      <subclass 
         name="com.qas.newmedia.intranet.iq.dto.schedules.DataAcquisitionSchedule" 
         discriminator-value="DataAcquisition">
                  
         <property name="productRelease" column="product_release" not-null="false" />
         <property name="dataInHouse" type="calendar" column="data_in_house" not-null="false" />
         <property name="dataInHouseFormat" column="data_in_house_format" not-null="false" />
         <property name="dataReviewed" type="calendar" column="data_reviewed" not-null="false" />
         <property name="dataReviewedFormat" column="data_reviewed_format" not-null="false" />
         <property name="contractReviewed" type="calendar" column="contract_reviewed" not-null="false" />
         <property name="contractReviewedFormat" column="contract_reviewed_format" not-null="false" />
         <property name="contractSigned" type="calendar" column="contract_signed" not-null="false" />
         <property name="contractSignedFormat" column="contract_signed_format" not-null="false" />
         
       </subclass>
      
      <!-- 
        - Project Schedule 
        -->      
      
      <subclass 
         name="com.qas.newmedia.intranet.iq.dto.schedules.ProjectSchedule" 
         discriminator-value="Project">
                  
         <property name="approval" type="calendar" column="approval" not-null="false" />
         <property name="approvalFormat" column="approval_format" not-null="false" />
         <property name="targetCompletion" type="calendar" column="target_completion" not-null="false" />
         <property name="targetCompletionFormat" column="target_completion_format" not-null="false" />
       </subclass>             
 
       <!-- 
        - New Media Schedule 
        -->      
      
      <subclass 
         name="com.qas.newmedia.intranet.iq.dto.schedules.NewMediaSchedule" 
         discriminator-value="NewMedia">
                  
         <property name="roi" column="roi" not-null="false" />
         <property name="implementor" column="implementor" not-null="false" />
         <property name="owningDepartment" column="owning_department" not-null="false" />
         <property name="targetStart" type="calendar" column="target_start" not-null="false" />
         <property name="targetStartFormat" column="target_start_format" not-null="false" />
         <property name="actualStart" type="calendar" column="actual_start" not-null="false" />
         <property name="actualStartFormat" column="actual_start_format" not-null="false" />
         <property name="targetCompletion" type="calendar" column="target_completion" not-null="false" />
         <property name="targetCompletionFormat" column="target_completion_format" not-null="false" />
         <property name="actualCompletion" type="calendar" column="actual_completion" not-null="false" />
         <property name="actualCompletionFormat" column="actual_completion_format" not-null="false" />         
         <property name="projectManagementScore" column="pm_score" not-null="false" />
         <property name="deliverableScore" column="del_score" not-null="false" />
       </subclass> 
 
    </class>

   <class name="com.qas.newmedia.intranet.iq.dto.schedules.ScheduleMilestone" 
      table="tbl_ScheduleMilestones">

        <id name="id" type="int" column="milestone_id" unsaved-value="0">
           <generator class="identity" />
       </id>

      <property name="created" type="calendar" not-null="true" />
      <property name="author" column="author" not-null="true" />
      <property name="target" type="calendar" not-null="true" />
      <property name="description" column="description" type="text" length="4096" not-null="true" />
      <property name="comment" column="comment" type="text" length="4096" not-null="true" />
      
      <many-to-one 
            name="schedule" 
            column="schedule_id" 
            not-null="true" />         
   </class>
   
   <class name="com.qas.newmedia.intranet.iq.dto.schedules.ScheduleUpdate" 
      table="tbl_ScheduleUpdates">

        <id name="id" type="int" column="update_id" unsaved-value="0">
           <generator class="identity" />
       </id>

      <property name="created" type="calendar" not-null="true" />
      <property name="author" column="author" not-null="true" />
      <property name="comment" column="comment" type="text" length="4096" not-null="true" />
      <property name="internal" column="internal" type="boolean" />
      
      <many-to-one 
            name="schedule" 
            column="schedule_id" 
            not-null="true" />         
   </class>
   
   <class name="com.qas.newmedia.intranet.iq.dto.schedules.ScheduleLink" 
      table="tbl_ScheduleLinks">

        <id name="id" type="int" column="link_id" unsaved-value="0">
           <generator class="identity" />
       </id>

      <property name="created" type="calendar" not-null="true" />
      <property name="label" column="label" not-null="true" />
      <property name="link" column="link" not-null="true" />
      
      <many-to-one 
            name="schedule" 
            column="schedule_id" 
            not-null="true" />
         
   </class>   

</hibernate-mapping> 

Code between sessionFactory.openSession() and session.close():

Code:

         Session session = HibernateUtil.getSession();
         Transaction tx = session.beginTransaction();

         // schedule
         
         schedule = (Schedule) session.load(Schedule.class, id);

         // updates
         
         List updates = session.find(
            "select elements(s.updates) from Schedule s where s.id = ?",
            new Integer(schedule.getId()),
             Hibernate.INTEGER
         );
         schedule.setUpdates(updates);         
         
         // milestones
         
         List milestones= session.find(
            "select elements(s.milestones) from Schedule s where s.id = ?",
            new Integer(schedule.getId()),
             Hibernate.INTEGER
         );
         schedule.setMilestones(milestones);         
         
         // links
         
         List links = session.find(
            "select elements(s.links) from Schedule s where s.id = ?",
            new Integer(schedule.getId()),
             Hibernate.INTEGER
         );
         schedule.setLinks(links);
         
         tx.commit();
         HibernateUtil.closeSession(session);

Full stack trace of any exception that occurs:
N/A
Name and version of the database you are using:
SQL Server 2000

Debug level Hibernate log excerpt:

Code:

Hibernate: select scheduleup0_.update_id as update_id1_, scheduleup0_.created as created1_, scheduleup0_.author as author1_, scheduleup0_.comment as comment1_, scheduleup0_.internal as internal1_, scheduleup0_.schedule_id as schedule6_1_, schedule1_.schedule_id as schedule1_0_, schedule1_.schedule_type as schedule2_0_, schedule1_.owner as owner0_, schedule1_.associates as associates0_, schedule1_.status as status0_, schedule1_.title as title0_, schedule1_.summary as summary0_, schedule1_.created as created0_, schedule1_.last_updated as last_upd9_0_, schedule1_.development_signoff as develop10_0_, schedule1_.development_signoff_format as develop11_0_, schedule1_.qat_signoff as qat_sig12_0_, schedule1_.qat_signoff_format as qat_sig13_0_, schedule1_.launch as launch0_, schedule1_.launch_format as launch_15_0_, schedule1_.product_release as product16_0_, schedule1_.data_in_house as data_in17_0_, schedule1_.data_in_house_format as data_in18_0_, schedule1_.data_reviewed as data_re19_0_, schedule1_.data_reviewed_format as data_re20_0_, schedule1_.contract_reviewed as contrac21_0_, schedule1_.contract_reviewed_format as contrac22_0_, schedule1_.contract_signed as contrac23_0_, schedule1_.contract_signed_format as contrac24_0_, schedule1_.approval as approval0_, schedule1_.approval_format as approva26_0_, schedule1_.target_completion as target_27_0_, schedule1_.target_completion_format as target_28_0_, schedule1_.roi as roi0_, schedule1_.implementor as impleme30_0_, schedule1_.owning_department as owning_31_0_, schedule1_.target_start as target_32_0_, schedule1_.target_start_format as target_33_0_, schedule1_.actual_start as actual_34_0_, schedule1_.actual_start_format as actual_35_0_, schedule1_.actual_completion as actual_36_0_, schedule1_.actual_completion_format as actual_37_0_, schedule1_.pm_score as pm_score0_, schedule1_.del_score as del_score0_ from dbSchedules.dbo.tbl_ScheduleUpdates scheduleup0_ left outer join dbSchedules.dbo.tbl_Schedules schedule1_ on scheduleup0_.schedule_id=schedule1_.schedule_id where scheduleup0_.update_id=?
Hibernate: select schedulemi2_.milestone_id as x0_0_ from dbSchedules.dbo.tbl_Schedules schedule0_, dbSchedules.dbo.tbl_ScheduleMilestones milestones1_, dbSchedules.dbo.tbl_ScheduleMilestones schedulemi2_ where schedule0_.schedule_id=milestones1_.schedule_id and milestones1_.milestone_id=schedulemi2_.milestone_id and ((schedule0_.schedule_id=? ))
Hibernate: select scheduleli2_.link_id as x0_0_ from dbSchedules.dbo.tbl_Schedules schedule0_, dbSchedules.dbo.tbl_ScheduleLinks links1_, dbSchedules.dbo.tbl_ScheduleLinks scheduleli2_ where schedule0_.schedule_id=links1_.schedule_id and links1_.link_id=scheduleli2_.link_id and ((schedule0_.schedule_id=? ))

shl · **Posted:** Wed Aug 18, 2004 3:35 am

Quote:

Therefore my question is how to apply ordering to my collection within HQL. My queries are all of the type select elements(collectionName), but I do not know how to apply ordering by a field in one of these collections.

you have:

Code:

select elements(s.updates) from Schedule s where s.id = ?

now, let it be:

Code:

select update from Schedule s
    left join s.updates update
where s.id=?
order by update.created asc

The main idea is to replace elements() by left join. The rest HQL queries for milestones and links should be the same.

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

thanks .. this helped me a lot ... but i ended up using

Code:

select update from Schedule s 
    inner join s.updates update 
where s.id=? 
order by update.created asc

because your version was returning a row when no updates were actually available (left outer join).

Thanks!!!