0 Replies Latest reply on Dec 21, 2006 6:30 AM by Richard Richter

    Hibernate, fetch-join + order by order problem

    Richard Richter Newbie

      Hello

      I have normal master-detail table pair where master table has mapping like this (important snippets):

      <hibernate-mapping>
       <class name="BE" table="be">
      ...
       <property name="stopTimestamp" type="java.util.Date" column="STOP_TS"
      not-null="false"/>
      
      ...
       <bag name="analysis" cascade="all" order-by="start_ts">
       <key column="be_id"/>
       <one-to-many class="BEAnalysis"/>
       </bag>
      ...
       </class>
      </hibernate-mapping>


      So I have analyzes ordered by their start_ts column. Now we need process big portion of BEs along with their analysis and we used fetch join in order to do it quickly (lazy load was unacceptably slow - too many selects).

      Code for listing those BE with analysis.

      Criteria criteria = session.createCriteria(BE.class);
      ...
      criteria.addOrder(Order.asc("stopTimestamp"));
      criteria.setFetchMode("analysis", FetchMode.JOIN);
      return (Collection<BE>) criteria.list();


      And now tail of the generated SQL:

      ... order by analysis2_.start_ts, this_.STOP_TS asc


      (this_ is be table of course)

      Problem is that I need to order by stop_ts first (be order is much more important, than comes the order of related analysis). What's the cure for this? Thanks for hint

      Virgo