CODEWITHSUNDEEP
nhibernatehql
sl3dg3
sl3dg3

Reputation: 5190

Help with HQL: Aggregate (count)

Given the model Activity containing a bag with models of type Report (one to many). I would like to get a list of all activities, containing the number of reports of each activity. This two queries don't lead to any good, the counter is always 1 (which is wrong):

select act, (select count(r) from act.Reports r) from Activity act

Or:

select act, count( elements(act.Reports) ) from Activity act group by act.ActivityId, act.Title

Is it possible to write a proper query in HQL to solve this easy task?

Thx for any tipps! sl3dg3

Edit: Following the mappings. Activity:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <class
        name="Core.Models.Config.Activity, Core"
        table="Activity"
    >

        <!-- primary key -->
        <id name="ActivityId" type="Int32" unsaved-value="0" access="property">
            <column name="ActivityId" not-null="true"/>
            <generator class="identity" />
        </id>

        <!-- Properties -->
        <many-to-one name="Title" fetch="join" cascade="all"/>

        <!-- One-To-Many Reports -->
        <bag name="Reports" inverse="true" fetch="join">
            <key column="ReportId" />
            <one-to-many class="Core.Models.Report"/>
        </bag>

    </class>
</hibernate-mapping>

Mapping Report:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <class
        name="Core.Models.Report, Core"
        table="Report"
    >

    <!-- primary key -->
    <id name="ReportId" type="Int32" unsaved-value="0" access="property">
        <column name="ReportId" not-null="true"/>
        <generator class="identity" />
    </id>

    <!-- Properties (shortened - there are more many-to-one and one bag -->
    <property name="Created" />
    <many-to-one name="Activity" column="ActivityId" />

</class>

Class Activity:

public class Activity
{

    public Activity()
    {
        Title = new Translation();
    }

    public virtual int ActivityId { get; set; }

    public virtual Translation Title { get; set; }

    public virtual IList<Report> Reports { get; set; }
}

Class Report:

public class Report
{

    /// <summary>
    /// HNIBERNATE ONLY
    /// </summary>
    public Report() 
    { }

    /// <summary>
    /// Init Report
    /// </summary>
    public Report(User author)
    {
        // ... Shortened
        Activity = new Activity();
    }

    public virtual Activity Activity { get; set; }

}

Upvotes: 1

Views: 1597

Answers (2)

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

What you want to achieve is something like this in SQL:

select 
  act.*, 
 (select count(*) from Reports rep where rep.id = act.reportId)
from Activity act

It would be easiest using size(), but unfortunately this is not working:

select act, size(act.Reports)
from Activity act

According to the docs, size is not available in the select clause. Interestingly, it actually works with .size, but not with size(), which should actually be equivalent:

select act, act.Reports.size
from Activity act

It may be worth a feature request to also make the function syntax (size()) working.

The officially working group by syntax is cumbersome, because you need to group by all mapped Activity properties:

select act, count(*)
from Activity act left join act.Reports rep
group by act.id, act.Name, act.Whatever

So I tried finally this variant and it seems to be exactly what you need:

select act, (select count(*) from act.Reports)
from Activity act

Upvotes: 2

Florian Lim
Florian Lim

Reputation: 5362

Your first HQL query has the wrong syntax. Try this instead:

select act, (select count(*) from act.Reports) from Activity act

Your second HQL query cannot work, because you would need all the columns in the GROUP BY clause. Try this instead:

select act.ActivityId, act.Title, count( elements(act.Reports) ) 
from Activity act 
group by act.ActivityId, act.Title

Edit:

Ah, I think this might be the bug:

<bag name="Reports" inverse="true" fetch="join">
    <key column="ActivityId" /> <-- instead of ReportId
    <one-to-many class="Core.Models.Report"/>
</bag>

Upvotes: 1

Related Questions