inner join with derived table

[agreif]

Hi,

is there a way to get this derived table SQL in esqueleto?

SELECT invoice.id, SumPayment
FROM invoice
INNER JOIN 
 (
   SELECT payment.invoice_id, SUM(payment.amount) AS SumPayment
   GROUP BY payment.invoice_id
   FROM payment
 ) AS payment
ON payment.invoice_id = invoice.id

I tried something like

  E.select $ E.from $ \(i `E.InnerJoin` p') -> do
    E.on (i E.^. InvoiceId E.==. p' E.^. PaymentInvoiceId)
    return ...
  where
    p' = E.sub_select $ E.from $ \p'' -> do
      E.groupBy $ p'' E.^. PaymentInvoiceId
      return (p'' E.^. PaymentInvoiceId, E.sum_ (p'' E.^. PaymentAmount))

But that does not compile

I would prefer a way without rawSql because I want to also join other tables.

thanks,
Alex.