Hibernate - Conditions are not applied in Inner Criteria Query

[Saravanan Vijayappan]

Hi,

Criteria 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:

      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:-

<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 code, hibernate generates the below 2 sqls and they are displayed in the console

Hibernate generated SQL query-1

 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:

 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
[ July 07, 2008: Message edited by: Saravanan Vijayappan ]

[Saravanan Vijayappan]

Any idea please ?