Scala Slick combining Rep sub queries into one re

I sum up totals in two different database tables:

val sum1Query: Rep[Int] = tableQuery1.map(_.amount).sum.ifNull(0)
val sum2Query: Rep[Int] = tableQuery2.map(_.amount).sum.ifNull(0)

for {
  sum1 <- sum1Query.result
  sum2 <- sum2Query.result
} yield {
  sum1 + sum2
}

This runs 2 SQL queries to the database each time .result is called. I am looking for a way to make it use only one SQL query.

Something like this doesn't work:

for {
  sum1 <- sum1Query
  sum2 <- sum2Query
} yield {
  sum1 + sum2
}.result

Any ideas on how to do it in Slick other than using plain SQL query?

Solution

Each call to .result creates a DBIO action which is a SQL statement. The trick to reducing the number of actions is to find a way to combine two queries (or two Reps) together into one action.

In your case you could zip the two queries:

val sum1 = table1.map(_.amount).sum.ifNull(0)
val sum2 = table2.map(_.amount).sum.ifNull(0)
val query = sum1.zip(sum2)

When run the query.result you'll execute a single query something like:

select ifnull(x2.x3,0), ifnull(x4.x5,0) 
from 
  (select sum("amount") as x3 from "table_1") x2, 
  (select sum("amount") as x5 from "table_2") x4

...which will result in a tuple of the two values.

However, as you've already just got a Rep[Int] you can use + in the database:

val query = sum1 + sum2

...which will be a query along the lines of:

select ifnull(x2.x3,0) + ifnull(x4.x5,0) 
from 
 (select sum("amount") as x3 from "table_1") x2, 
 (select sum("amount") as x5 from "table_2") x4