Criteria Restriction not working properly

saravvij · **Joined:** Sun Jul 06, 2008 9:32 pm **Posts:** 6

Hi,

Criteria query restrictions are not applied in the items of the collections. I have a persistence entity (POJO) Menugroup and it has a collection called catgroups (HashSet, one to many). Catgroup persistence entity (POJO) has another one-to-many collection "dealitems" (HashSet). My criteria query is below:

code:

Code:

          List menuGroups = session.createCriteria(Menugroup.class)
          .add( Restrictions.eq("published", true)).addOrder( Property.forName("name").asc()).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).
          setFetchMode("catgroups", FetchMode.JOIN).
          createCriteria("catgroups").addOrder( Property.forName("name").asc()).
          add( Restrictions.eq("published", true) ).setFetchMode("dealitems", FetchMode.DEFAULT)
          .list();

Mapping file snippet of Menugroup.hbm.xml is below:-

code:

<set inverse="true" batch-size="10" lazy="false" name="catgroups">
<key>
<column name="MENUGROUP_ID">
<comment>Menu Group relationship</comment>
</column>
</key>
<one-to-many class="com.model.persistence.Catgroup"/>
</set>

When I run my Criteria query, hibernate generates the below 2 sqls and they are displayed in the console

Hibernate generated SQL query-1

code:

Code:

 select
            this_.MENUGROUP_ID as MENUGROUP1_0_1_,
            this_.NAME as NAME0_1_,
            this_.DESCRIPTION as DESCRIPT3_0_1_,
            this_.PUBLISHED as PUBLISHED0_1_,
            this_.LASTUPDATE as LASTUPDATE0_1_,
            catgroup1_.CATGROUP_ID as CATGROUP1_1_0_,
            catgroup1_.MENUGROUP_ID as MENUGROUP2_1_0_,
            catgroup1_.NAME as NAME1_0_,
            catgroup1_.DESCRIPTION as DESCRIPT4_1_0_,
            catgroup1_.IMGURL as IMGURL1_0_,
            catgroup1_.PUBLISHED as PUBLISHED1_0_,
            catgroup1_.SUGESSTION as SUGESSTION1_0_,
            catgroup1_.LASTUPDATE as LASTUPDATE1_0_
        from
            cyddev.MENUGROUP this_
        inner join
            cyddev.CATGROUP catgroup1_
                on this_.MENUGROUP_ID=catgroup1_.MENUGROUP_ID
        where
            this_.PUBLISHED=?
            and catgroup1_.PUBLISHED=?
        order by
            this_.NAME asc,
            catgroup1_.NAME asc

Hibernate generated SQL query-2:

code:

Code:

     select
            catgroups0_.MENUGROUP_ID as MENUGROUP2_1_,
            catgroups0_.CATGROUP_ID as CATGROUP1_1_,
            catgroups0_.CATGROUP_ID as CATGROUP1_1_0_,
            catgroups0_.MENUGROUP_ID as MENUGROUP2_1_0_,
            catgroups0_.NAME as NAME1_0_,
            catgroups0_.DESCRIPTION as DESCRIPT4_1_0_,
            catgroups0_.IMGURL as IMGURL1_0_,
            catgroups0_.PUBLISHED as PUBLISHED1_0_,
            catgroups0_.SUGESSTION as SUGESSTION1_0_,
            catgroups0_.LASTUPDATE as LASTUPDATE1_0_
        from
            cyddev.CATGROUP catgroups0_
        where
            catgroups0_.MENUGROUP_ID in (
                ?, ?
            )

The issue issue is, in the result, items in the catgroups collections are not in ascending order and the restriction "published=true" are also not applied on them. I meant to say that below condtions from my criteria query are not applied on catgroups collections.

createCriteria("catgroups").addOrder( Property.forName("name").asc()).
add( Restrictions.eq("published", true) )

The above critieria conditions should be applied on hibernate generated 2nd SQL query but it does not happen. Could any one help me to find out whether I have missed some thing in my criteria query to get the result as I expected or this is a bug in criteria query. ?

Expected result: Items in the catgroups collection should be in order and the condition "published"=true should applied.

I hope, the information i provided above would help to understand what is the problem I face.

Thanks
Sarav

sivakami · **Posted:** Tue Sep 01, 2009 4:41 am

Hi,

I'm facing the same issue as above.
Can anyone please provide a solution for this.

Thanks

wolli · **Posted:** Tue Sep 01, 2009 7:49 am

Have a look at the end of this section:

http://nhforge.org/doc/nh/en/index.html#querycriteria-associations

The restrictions are only applied to find the appropriate main objects of the criteria. In your case that's Menugroup. All matching menugroups will be fully loaded.

wolli · **Posted:** Tue Sep 01, 2009 7:50 am

If you want a order in your collection, you can specifiy an order by in the mapping file.

Cameron McKenzie · **Posted:** Wed Sep 02, 2009 6:32 pm

Ordering can also be done with the Criteria API:

Code:

    User user = new User();
    Session session = HibernateUtil.beginTransaction();
    Criteria criteria = session.createCriteria(User.class);
    Order order = Order.asc("loginName");
    criteria.addOrder(order);
    List results = criteria.list();
    HibernateUtil.commitTransaction();
    for (int i = 0; i<results.size(); i++) {
       System.out.println(results.get(i).toString());
    }
  }

or a simple orderby in the HQL:

Code:

Session session = HibernateUtil.beginTransaction();
String hql = "from User as u ORDER BY u.id ASC";
Query query = session.createQuery(hql);
List users = query.list();
for (int i = 0; i < users.size(); i++) {
  User user = (User) users.get(i);
  System.out.println("Hello World");
  System.out.println(user.getLoginName());
}
HibernateUtil.commitTransaction();

sivakami · **Posted:** Fri Sep 04, 2009 2:43 am

Hi wolli/Cameron,

Thanks for the reply. It was helpful.