Polymorphic Query Abstraction

Continuing from the section on SQL Fragment Functions, captured functions can use generics and polymorphism in order to create highly abstractable query components. For example:

interface Locateable {
  val locationId: Int
}
data class Person(val name: String, val age: Int, locationId: Int)
data class Robot(val model: String, val createdOn: LocalDate, val locationId: Int)
data class Address(val id: Int, val street: String, val zip: String)

// Now let's create a captured function that can be used with any Locateable object:
@SqlFragment
fun <L : Locateable> joinLocation(locateable: SqlQuery<L>): SqlQuery<Pair<L, Address>> =
  sql.select {
    val l = from(locateable)
    val a = join(addresses) { a -> a.id == l.locationId }
    l to a
  }

Now I can use this function with the Person table

val people: SqlQuery<Pair<Person, Address>> = sql {
  joinLocation(Table<Person>().filter { p -> p.name == "Joe" })
}
people.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.name, p.age, p.locationId, a.id, a.street, a.zip FROM Person p JOIN Address a ON a.id = p.locationId WHERE p.name = 'Joe'

As well as the Robot table:

val robots: SqlQuery<Pair<Robot, Address>> = sql {
  joinLocation(Table<Robot>().filter { r -> r.model == "R2D2" })
}
//> SELECT r.model, r.createdOn, r.locationId, a.id, a.street, a.zip FROM Robot r JOIN Address a ON a.id = r.locationId WHERE r.model = 'R2D2'

You can then continue to compose the output of this function to get more and more powerful queries!