Using NHibernate to report a table with two sums

Question

I have three tables: People, Purchases, Payments with 1-to-many relationships between People and Purchases, and People and Payments.

I want to generate a report of People showing the sum of their Purchases and Payments.

I can easily generate a report for all people showing the sum of the payments or purchases, vis:

var query = 
  DetachedCriteria.For<People>("People")
  .CreateAlias("Payments", "paymentsMade");

query.SetProjection(Projections.ProjectionList()
  .Add(Projections.GroupProperty("Id"), "Id")
  .Add(Projections.Sum("paymentsMade.Amount"), "TotalPayments")

Can I do this in a single query in NHibernate? Either using the criteria API (preferred) or HQL.

Answer 1

Try something like this:

var query = @"select
                (select sum(pu.Amount) from Purchase pu where pu.People.Id = ppl.Id),
                (select sum(pa.Amount) from Payments pa where pa.People.Id = ppl.Id) 
              from People ppl";

var results = session
                .CreateQuery(query)
                .List();

Or perhaps using ad-hoc mapping:

public class BulkReport
{
    public double Purchases { get; set; }
    public double Payments  { get; set; }
}

var results = session
                .CreateQuery(query)
                .SetResultTransformer(Transformers.AliasToBean(typeof(BulkReport)))
                .List<BulkReport>();

Another option would be using MultiQueries.