count childcollection

doggelito · **Joined:** Mon Jan 14, 2008 10:58 am **Posts:** 24

I've seen many examples that counts a collection with select count(*) just like the documentation example (Chapter 14:16):
http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html

Code:

( (Integer) session.iterate("select count(*) from ....").next() ).intValue()

But I don't understand how to count a child collection!?
Can someone please direct me?

My code:

Code:

IList<Category> categories = session.CreateCriteria(typeof(Category)).List<Category>();

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
   <class name="Swh.Entities.Category, Swh.Entities" table="Categories">
      <id name="Id" column="CategoryId" type="int" unsaved-value="-1">
         <generator class="identity" />
      </id>
      <many-to-one name="Parent" column="ParentId" />
      <bag name="Childs">
         <key column="ParentId"/>
         <one-to-many class="Swh.Entities.Category, Swh.Entities"/>
      </bag>      
      <property name="Name" column="CategoryName" type="String"/>
      <bag name="Products">
         <key column="CategoryId"/>
         <one-to-many class="Swh.Entities.Product, Swh.Entities"/>
      </bag>
   </class>
</hibernate-mapping>


<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
   <class name="Swh.Entities.Product, Swh.Entities" table="Products">
      <id name="Id" column="ProductId" type="int" unsaved-value="-1">
         <generator class="identity" />
      </id>
      <property name="Name" column="ProductName" type="String"/>
      <many-to-one name="Category" class="Swh.Entities.Category, Swh.Entities" column="CategoryId" />
   </class>
</hibernate-mapping>

In my IList<Category> I want to count how many products the category have and list in a column in a gridview.

darioquintana · **Posted:** Tue Jan 22, 2008 10:43 am

Hi,

If it a non-larger collection you can make the query and later use myChildCollection.Count.

In other hand, you can use 'formula' for that. Add a integer property to your class, and do the select there:

...
<property name="ChildCount" formula="select count(*) ... "/>
...

Best regards

doggelito · **Joined:** Mon Jan 14, 2008 10:58 am **Posts:** 24

/EDIT

Thank you darioquintana, we posted at the same time! ;)

I think I solved it! :)
I added a property with a formula in my Category mapping file.

Code:

<property name="ProductCount" formula="(select count(*) from Products p where p.CategoryId = CategoryId )" access="property"/>

Now I just hope this wont affect insert/update commands! But I'll come to that later! :)

darioquintana · **Posted:** Tue Jan 22, 2008 11:39 am

;)

Best Regards