Hibernate Subquery Question

Question

This should be a simple one I hope.

I have an invoice and that invoice has a list of payments.

Using the Criteria API I am trying to return a list of invoices and their payment total. So, in SQL I want something like this:

SELECT i.*, (SELECT SUM(PMT_AMOUNT) FROM INVOICE_PAYMENTS p WHERE p.INVOICE = i.INVOICE) FROM INVOICES i

I can't for the life of me figure out how to achieve this with the Criteria API. Doing something like:

Criteria crit = session.createCriteria(Invoice.class)
criteria.setProjection(Projections.projectionList()
          .add(Projections.sum("payements.paymentAmount").as("paymentTotal"))

Simply returns 1 row with the projected payment total for all invoices, which is actually what you'd expect, but this is as close as I can get.

Any help is greatly appreciated.

Answer 1

I'm pretty sure you can't return entities in a Projection.

There are two possibles:

• Run two criteria queries, one for the actual invoices and one for there totals
• Use HQL to perform the query

I haven't tested this but it should go something like:

select i, (select sum(p.amount) from InvoicePayments p where p.invoice = i.invoice) from Invoice i

Will have to wait until tomorrow, I have a very similar data structure at work I should be able to test this then.

Answer 2

There is a way with Criteria to return a list of Invoices along with the total payments for that invoice.

In theory, the answer is that you can use a grouping property on a projection query to group the result into total payment by invoice. The second part is that you could use a transient "totalPayment" value on the Invoice and select the projection into the Invoice structure using a transformer. This would be easier than dealing with an ArrayList of different properties but would depend on what you needed to use the results for.

To demonstrate this, here is the important part of a small Invoice class:

public class Invoice{
   private String name;

   @Transient private int totalPayments;
   @OneToMany Set<Payment> payments = new HashSet<Payment>();

   // getters and setters
...
}

Then this is the criteria that you could use

Criteria criteria = session.createCriteria(Invoice.class)
            .createAlias("payments", "pay")
            .setProjection(Projections.projectionList()
                .add(Projections.groupProperty("id"))
                .add(Projections.property("id"), "id")
                .add(Projections.property("name"), "name")
                .add(Projections.sum("pay.total").as("totalPayments")))
            .setResultTransformer(Transformers.aliasToBean(Invoice.class));

List<Invoice> projected = criteria.list();

And this is the sql that is generated

Hibernate: 
   select this_.id as y0_, 
          this_.id as y1_, 
          this_.name as y2_, 
          sum(pay1_.total) as y3_ 
   from invoice this_ 
   inner join invoice_payment payments3_ on this_.id=payments3_.invoice_id 
   inner join payment pay1_ on payments3_.payments_id=pay1_.id 
   group by this_.id