Databases
StackOverflow2013
Postsdbo
POHelp with HQL: Aggregate (count)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHelp with HQL: Aggregate (count)
    text
    copied!<p>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):</p> <pre><code>select act, (select count(r) from act.Reports r) from Activity act </code></pre> <p>Or:</p> <pre><code>select act, count( elements(act.Reports) ) from Activity act group by act.ActivityId, act.Title </code></pre> <p>Is it possible to write a proper query in HQL to solve this easy task?</p> <p>Thx for any tipps! sl3dg3</p> <p>Edit: Following the mappings. Activity:</p> <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt; &lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"&gt; &lt;class name="Core.Models.Config.Activity, Core" table="Activity" &gt; &lt;!-- primary key --&gt; &lt;id name="ActivityId" type="Int32" unsaved-value="0" access="property"&gt; &lt;column name="ActivityId" not-null="true"/&gt; &lt;generator class="identity" /&gt; &lt;/id&gt; &lt;!-- Properties --&gt; &lt;many-to-one name="Title" fetch="join" cascade="all"/&gt; &lt;!-- One-To-Many Reports --&gt; &lt;bag name="Reports" inverse="true" fetch="join"&gt; &lt;key column="ReportId" /&gt; &lt;one-to-many class="Core.Models.Report"/&gt; &lt;/bag&gt; &lt;/class&gt; &lt;/hibernate-mapping&gt; </code></pre> <p>Mapping Report:</p> <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt; &lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"&gt; &lt;class name="Core.Models.Report, Core" table="Report" &gt; &lt;!-- primary key --&gt; &lt;id name="ReportId" type="Int32" unsaved-value="0" access="property"&gt; &lt;column name="ReportId" not-null="true"/&gt; &lt;generator class="identity" /&gt; &lt;/id&gt; &lt;!-- Properties (shortened - there are more many-to-one and one bag --&gt; &lt;property name="Created" /&gt; &lt;many-to-one name="Activity" column="ActivityId" /&gt; &lt;/class&gt; </code></pre> <p></p> <p>Class Activity:</p> <pre><code>public class Activity { public Activity() { Title = new Translation(); } public virtual int ActivityId { get; set; } public virtual Translation Title { get; set; } public virtual IList&lt;Report&gt; Reports { get; set; } } </code></pre> <p>Class Report:</p> <pre><code>public class Report { /// &lt;summary&gt; /// HNIBERNATE ONLY /// &lt;/summary&gt; public Report() { } /// &lt;summary&gt; /// Init Report /// &lt;/summary&gt; public Report(User author) { // ... Shortened Activity = new Activity(); } public virtual Activity Activity { get; set; } } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload