how to sort a list by size of associated collect object

wenqi · **Joined:** Fri Mar 24, 2006 6:46 pm **Posts:** 1

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hi I have the one to many relationshop between user and task object mapped in follow file:

Mapping documents:

<hibernate-mapping>
<class name="com.abc.model.User" table="User">
<id name="id" column="User_ID" unsaved-value="null">
<generator class="native"/>
</id>
<property name="name" column="User_Name" not-null="true"/>
<property name="startDate" column="Start_Date" not-null="true" type="date"/>
<property name="endDate" column="End_Date" not-null="true" type="date"/>
<set name="tasks" inverse="true" lazy="false">
<key column="User_ID"/>
<one-to-many class="com.abc.model.Task"/>
</set>
</class>
</hibernate-mapping>

and I want to use Criteria API get user list and sort the list by the size of tasks (collection object in User Object). It likes:

User name nubmer of Tasks (size of tasks associated with user)
A 5

B 4

C 3

could anyone help me out here and give me some hint to do it? thank you in advance.

jt_1000 · **Posted:** Fri Mar 24, 2006 8:34 pm

don't know how to do it in Criteria off hand, but here is an SQLQuery by example:

Code:

SQLQuery q=sess.createSQLQuery("select {Person.*},count(a.address_id) cnt " +
                        " from SAMPLE1_PERSON Person, Sample1_ADDRESS a" +
                        " where Person.person_id=a.person_id" +
                        " group by Person.PERSON_ID,  " +
                        "Person.FIRST_NAME, " +
                        "Person.LAST_NAME, " +
                        "Person.created");
boolean ret=true;
q.addEntity("Person",Person.class);
q.addScalar("cnt",Hibernate.LONG);
List l=q.list();
Iterator itr=l.iterator();
Object obj[]=null;
Person p=null;
Long cnt=null;
while(itr!=null&&itr.hasNext())
{
   obj=(Object[]) itr.next();
   cnt=(Long) obj[0];
   p=(Person) obj[1];
   System.out.println("p.getPERSON_ID() = " + p.getPERSON_ID());
   System.out.println("p.getFIRST_NAME() = " + p.getFIRST_NAME());
   System.out.println("cnt = " + cnt);
}

Perhaps you can model the Criteria building after this examples.