Subqueries

You can use a combine filter and map functions to create correlated subqueries. For example, let’s say we want to find all the people over the average age:

val q = sql {
  Table<Person>().filter { p -> p.age > Table<Person>().map { it.age }.avg() }
}
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.age > (SELECT avg(p1.age) FROM Person p1)

Recall that you could table the .map function with table aggregations. Let’s say you want to get not average but also use that together with another aggreagtor (e.g. the average minus the minimum). Normally you could use an expression avg(p.age) + min(p.age) with a the .map function.

val customExpr: SqlQuery<Double> = sql.select { Table<Person>().map { p -> avg(p.age) + min(p.age) } }

If you want to use a statement like this inside of a correlated subquery, we can use the .value() function inside of a capture block to convert a SqlQuery<T> into just T.

val q = sql {
  Table<Person>().filter { p -> p.age > Table<Person>().map { p -> avg(p.age) + min(p.age) }.value() }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.age > (SELECT avg(p1.age) + min(p1.age) FROM Person p1)