Hibernate Subquery Question

Timboson · **Joined:** Wed Oct 01, 2008 8:42 pm **Posts:** 12

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.

Froestel · **Posted:** Wed Oct 14, 2009 4:44 pm

Hi Timboson,

I'd guess you'll need a grouping statement and another entry point (if possible)

Code:

Criteria crit = session.createCriteria(InvoicePayment.class);
crit.setProjection(
  Projections.projectionList()
    .add(Projections.groupProperty("invoice"))
    .add(Projections.sum("payments.paymentAmount").as("paymentTotal")
  )

If you query the (hopefully existing) InvoicePayment class you can group by the reference to Invoice. Then, the sum runs only over the paymentAmount values of payments associated with that invoice.

CU
Froestel

Timboson · **Joined:** Wed Oct 01, 2008 8:42 pm **Posts:** 12

Hi Froestel,

Thanks, this is helpful, but I'm worried that in the future I may want to expand on the query to join on other related tables as well.

For instance:

Code:

SELECT 
    i.*, 
    (SELECT SUM(PMT_AMOUNT) FROM INVOICE_PAYMENTS p WHERE p.INVOICE = i.INVOICE), 
    (SELECT SUM(FND_AMOUNT) FROM INVOICE_FUNDS f WHERE f.INVOICE = i.INVOICE AND f.FUND =  2) 
FROM INVOICES i

Which then breaks this case, as I wouldn't be able to create a criteria on both funds and payments.

Any idea on how to solve this case?

Thanks,
Tim

Froestel · **Posted:** Sat Oct 17, 2009 9:37 am

Hi Timboson,

I don't see any trouble appearing for the future. You always want to sum up values that have in common the same invoice.id value if I get you right. As long as that is the case you can go on group by invoice.id an add sums on as many joined tables as you like (except the DBMS imposes restrictions on that).
Something like that should do:

Code:

SELECT i.id, sum(p.PMT_AMOUNT), sum(f.FND_AMOUNT)
FROM INVOICES i
LEFT JOIN INVOICE_PAYMENTS p ON p.INVOICE = i.INVOICE
LEFT JOIN INVOICE_FUNDS f ON f.INVOICE = i.INVOICE
GROUP BY i.id

CU
Froestel