How to perform SQL "SELECT SUM(item.cost * item.amount)... " query

Question

im trying to write equivalent of SQL query:

SELECT tax.name as tax, SUM(item.cost * item.amount) as total FROM Invoices inv
JOIN InvoiceItems item ON( item.invoice = inv.id )
JOIN Taxes tax ON( tax.id = it.tax )
WHERE inv.id = 1
GROUP BY tax.id

I can't figure out how to "add" total column to the query, my code is as follows

val res = Invoices
            .innerJoin(InvoiceItems, { Invoices.id }, { InvoiceItems.invoice })
            .innerJoin(Taxes, { InvoiceItems.tax }, { Taxes.id })
            .slice( Taxes.name.alias("tax"), InvoiceItems.cost, InvoiceItems.amount )
            .select { Invoices.id eq 1 }

Is it even possible to do it this way or i have to do it later in code?

Answer 1

You can use TimesOp within Expression.build{} block, like this:

    val total = Expression.build { Invoices.cost * Invoices.amount }
    val taxAndTotal = Invoices.innerJoin(InvoiceTaxes).innerJoin(Taxes)
        .slice(Taxes.name, total)
        .select{ Invoices.id eq 1 }
        .groupBy(Taxes.id)
        .map { it[Taxes.name] to it[total] }