ordering results by collection size

bigor · **Joined:** Sat Feb 18, 2006 6:24 am **Posts:** 23

I want to order my results by the size of their child collection. there is example on Hibernate Tips and Tricks and it says this.

select user
from User user
left join user.messages msg
group by user
order by count(msg)

so, its ok, but I am using Criteria api and my query looks like this:

Criteria cr=session.createCriteria(hr.klopa.domain.FoodProvider.class);

Foodprovider has collection "menuGroups", so I want to order foodproviders with most menuGroups on top.
I tried something like this but it doesn't work

cr.addOrder(Order.asc("menuGroups.size"));

I get error which says that there is no property called size in menuGrops object.

So, how can i do this with criteria api?

rkhanmoh · **Posted:** Tue May 30, 2006 11:25 am

is there a method called getSize on your menuGroups object?

bigor · **Joined:** Sat Feb 18, 2006 6:24 am **Posts:** 23

rkhanmoh wrote:

is there a method called getSize on your menuGroups object?

menuGroups is collection, HashSet to be more specific.

rkhanmoh · **Posted:** Wed May 31, 2006 12:16 pm

don't know off hand then, sorry, never really tried it

ramnath · **Posted:** Thu Jun 01, 2006 5:24 am

Hi

Try this.

Criteria cr =session.createCriteria(hr.klopa.domain.FoodProvider.class);
cr.createAlias( "menuGroups", "mgs" );
cr.addOrder( Order.asc("mgs.size") );

bigor · **Joined:** Sat Feb 18, 2006 6:24 am **Posts:** 23

no, it doesn't help!

bkmr_77 · **Joined:** Tue Apr 25, 2006 12:04 pm **Posts:** 260

I am sure this is not what you want, but atleast it will give you a hint.

Code:

Criteria cr =session.createCriteria(hr.klopa.domain.FoodProvider.class, "fp" ); 
cr.createAlias( "menuGroups", "mgs" ); 

ProjectionList pl = Projections.projectionList();
pl.add( Projections.count( "msgs.SOME_PROPERTY", "countMsgs" ) );
pl.add( Projections.groupProperty( "fp.propertyOne" );
pl.add( Projections.groupProperty( "fp.propertyTwo" );

cr.setProjection( pl );
cr.addOrder( Order.asc("countMsgs") );

bigor · **Joined:** Sat Feb 18, 2006 6:24 am **Posts:** 23

it doesn't help ...because I need whole object and i dont want to change this query every time I change FoodProvider class.

but there must be way to this, since it's possible with HQL...

ramnath · **Posted:** Fri Jun 02, 2006 1:04 pm

Hi,

Give us the correct SQL or HQL Query. So that we would be able to translate it into Criteria.

bigor · **Joined:** Sat Feb 18, 2006 6:24 am **Posts:** 23

select fp
from FoodProvider fp
left join fp.menuGroups mgs
group by fp
order by count(mgs)

this it taken from HIbernate Tips and tricks section...I just replaced User with FoodProvider and messages with menuGroups

ramnath · **Posted:** Sat Jun 03, 2006 3:22 am

Hi,

Try this,

Criteria cr =session.createCriteria(hr.klopa.domain.FoodProvider.class)
.setFetchMode("menuGroups",FetchMode.EAGER);

ProjectionList pl = Projections.projectionList()
.add( Projections.groupProperty("fp.propertyOne"))
.add( Projections.groupProperty("fp.propertyTwo" ));
crt.setProjection(pl);

cr.createAlias( "menuGroups", "mgs" );
ProjectionList pl1 = Projections.projectionList()
.add(Projections.property("mgs.SOME_PROPERTY"), "countmsg")
.add(Projections.count("countmsg"));
crt.setProjection(pl1).addOrder(Order.asc("countmsg")).list();

If this does't help give us the full mapping files of FoodProvider and MenuGroups with some explanation in detail[/b]

bigor · **Joined:** Sat Feb 18, 2006 6:24 am **Posts:** 23

Hi,

this is my current method

Code:

public List getFoodProvidersOld(long block,long foodCategory,Boolean isDelivery,Boolean isRestaurant,Boolean isCatering,int pageStart,int pageSize) throws DAOException
    {
        Session session = HibernateUtil.currentSession();
        try
        {
            Criteria cr=session.createCriteria(hr.klopa.domain.FoodProvider.class); 
             
            cr.add( Expression.eq("active",Boolean.TRUE) );
            
            if(isDelivery!=null)
            {
                cr.add( Expression.eq("delivery",isDelivery) );
            }
            
            if(isRestaurant!=null)
            {
                cr.add( Expression.eq("restaurant",isRestaurant ) );
            }
            
            if(isCatering!=null)
            {
                cr.add( Expression.eq("catering",isCatering  ) );
            }
            
            if(block>0)
            {
                cr.createCriteria("blocks").add( Restrictions.eq("id",block ) );
            }
            
            if(foodCategory>0)
            {
                FoodCategory fc = new FoodCategory();
                fc.setId(foodCategory);
                cr.createCriteria("menuGroups").add( Restrictions.eq("foodCategory",fc ) );
            }
            
            cr.addOrder(Order.asc("name"));
            cr.setFirstResult(pageStart);
            cr.setMaxResults(pageSize);
        
            List list = cr.list();
            
            return list;
        } 
        catch (HibernateException e)
        {
            throw e;
        } 
        finally
        {
            try{HibernateUtil.closeSession(session);}catch (Exception e){}
        }
    
    }

and this is the mapping of FoodProvider:

Code:

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

<hibernate-mapping default-lazy="true">

   <class name="hr.klopa.domain.FoodProvider" table="k_foodprovider">
      <id name="id">
         <column name="k_id" not-null="true" />
         <generator class="native" />
      </id>

      <property name="name">
         <column name="k_name" length="255" not-null="false"/>
      </property>
      
      <property name="street">
         <column name="k_street" length="255" not-null="false"/>
      </property>
      
      <property name="workHours">
         <column name="k_workHours" length="255" not-null="false"/>
      </property>
      
      <property name="owner">
         <column name="k_owner" length="255" not-null="false"/>
      </property>
      
      <property name="telephoneForOrders">
         <column name="k_telephoneForOrders" length="255" not-null="false"/>
      </property>
      
      <property name="webAddress">
         <column name="k_webAddress" length="255" not-null="false"/>
      </property>
      
      <set name="menuGroups" table="k_menugroup" cascade="all" order-by="k_id asc">
         <key column="k_foodprovider"/>
         <one-to-many class="hr.klopa.domain.MenuGroup"/>
      </set>
      
      <set name="images" table="k_image" cascade="all" order-by="k_id asc">
         <key column="k_foodprovider"/>
         <one-to-many class="hr.klopa.domain.Image"/>
      </set>
      
      <set name="blocks" table="k_foodprovider_blocks">
         <key column="k_foodprovider"/>
         <many-to-many class="hr.klopa.domain.Block" column="k_block"/>
      </set>
      
      <property name="delivery">
         <column name="k_isdelivery" sql-type="int"/>
      </property>
      
      <property name="restaurant">
         <column name="k_isrestaurant" sql-type="int"/>
      </property>
      
      <property name="catering">
         <column name="k_iscatering" sql-type="int"/>
      </property>
      
      <property name="active">
         <column name="k_isactive" sql-type="int"/>
      </property>
      
      <property name="grade">
         <column name="k_grade"/>
      </property>
      
      <property name="note">
         <column name="k_note" length="1000" not-null="false"/>
      </property>

   </class>
   
</hibernate-mapping>
   

and for MenuGroup

Code:

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

<hibernate-mapping default-lazy="true">

   <class name="hr.klopa.domain.MenuGroup" table="k_menugroup">
   
      <id name="id">
         <column name="k_id" not-null="true" />
         <generator class="native" />
      </id>
      
      <property name="title">
         <column name="k_title" length="255" not-null="false"/>
      </property>
      
      <property name="note">
         <column name="k_note" length="1000" not-null="false"/>
      </property>
      
      <many-to-one name="foodCategory" column="k_foodcategory" class="hr.klopa.domain.FoodCategory" not-null="true"/>

      <set name="foodItems" table="k_fooditems" cascade="all" order-by="k_id asc">
         <key column="k_menugroup"/>
         <one-to-many class="hr.klopa.domain.FoodItem"/>
      </set>
      
      <many-to-one name="foodProvider" column="k_foodprovider" class="hr.klopa.domain.FoodProvider" not-null="false"/>
      
      <property name="number">
         <column name="k_number" not-null="false"/>
      </property>
      
      <property name="priceTitle">
         <column name="k_pricetitle" length="255" not-null="false"/>
      </property>
      
      <property name="price2Title">
         <column name="k_price2title" length="255" not-null="false"/>
      </property>
      
      <property name="price3Title">
         <column name="k_price3title" length="255" not-null="false"/>
      </property>
   
   </class>
   
</hibernate-mapping>
   