Basic Operations

Map

This is also known as an SQL projection. It allows you to select a subset of the columns in a table.

val q = sql {
  Table<Person>().map { p -> p.name }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.name FROM Person p

You can also use the .map funtion to perform simple aggreations on tables. For example:

val q = sql {
  Table<Person>().map { p -> Triple(min(p.age), max(p.age), avg(p.age)) }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT min(p.age), max(p.age), avg(p.age) FROM Person p

Filter

This is also known as a SQL where clause. It allows you to filter the rows in a table.

val q = sql {
  Table<Person>().filter { p -> p.name == "Joe" }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.name = 'Joe'

You can also do .where { name == "Joe" } for a slightly more SQL-diomatic experience but this function is not as powerful.

Also, if you are using a sql.select block, you can also use the where function to filter the rows:

val q = sql.select {
  val p = from(Table<Person>())
  where { p.name == "Joe" }
}