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.
Hi,
is there a way to get this derived table SQL in esqueleto?
I tried something like
But that does not compile
I would prefer a way without rawSql because I want to also join other tables.
thanks, Alex.