Joins

Use the sql.select to do as many joins as you need.

Inner Join

val q: SqlQuery<Pair<Person, Address>> =
  sql.select {
    val p = from(Table<Person>())
    val a = join(Table<Address>()) { a -> a.ownerId == p.id }
    p to a
  }
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.name, p.age, a.ownerId, a.street, a.zip FROM Person p JOIN Address a ON a.personId = p.id

Left Join

Let’s add a left-join:

val q: SqlQuery<Pair<Person, Address?>> =
  sql.select {
    val p = from(Table<Person>())
    val a = join(Table<Address>()) { a -> a.ownerId == p.id }
    val f = joinLeft(Table<Furnitire>()) { f -> f.locatedAt == a.id }
    Triple(p, a, f)
  }
//> SELECT p.id, p.name, p.age, a.ownerId, a.street, a.zip, f.name, f.locatedAt FROM Person p 
//  LEFT JOIN Address a ON a.personId = p.id 
//  LEFT JOIN Furnitire f ON f.locatedAt = a.id

Notice that the Address table is now nullable. This is because the left-join can return null values.

What can go inside sql.select

What can go inside of the sql.select function is very carefully controlled by ExoQuery. It needs to be one of the following:

• A from statement
• A join or leftJoin statement
• A where clause
• A sortBy clause
• A groupBy clause

You can use all of these features all together. For example:

val q: SqlQuery<Pair<Person, Address>> =
  sql.select {
    val p = from(Table<Person>())
    val a = join(Table<Address>()) { a -> a.ownerId == p.id }
    where { p.name == "Joe" }
    sortBy(p.name to Asc, p.age to Desc)
    groupBy(p.name)
    p to a
  }

q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.name, p.age, a.ownerId, a.street, a.city FROM Person p 
//  INNER JOIN Address a ON p.id = a.ownerId 
//  WHERE p.age > 18 GROUP BY p.age, a.street ORDER BY p.age ASC, a.street DESC

Implicit Joins

Also note that you can do implicit joins using the sql.select function if desired as well. For example, the following query is perfectly reasonable:

val q = sql.select {
  val p = from(Table<Person>())
  val a = from(Table<Address>())
  val r = from(Table<Robot>())
  where {
    p.id == a.ownerId && p.id == r.ownerId && p.name == "Joe"
  }
  Triple(p, a, r)
}
//> SELECT p.id, p.name, p.age, a.ownerId, a.street, a.zip, r.ownerId, r.model FROM Person p, Address a, Robot r 
//  WHERE p.id = a.ownerId AND p.id = r.ownerId AND p.name = 'Joe'