Dynamic Queries

There are certain situations where ExoQuery cannot generate a query at compile-time. Most notably this happens when runtime values are used to choose a particular instance of SqlQuery or SqlExpression to be used. For example:

val someFlag: Boolean = someRuntimeLogic()
val q = sql {
  if (someFlag) {
    Table<Person>().filter { p -> p.name == "Joe" }
  } else {
    Table<Person>().filter { p -> p.name == "Jim" }
  }
}

ExoQuery does not know the value of someFlag at compile-time and therefore cannot generate a query at that point. This means that ExoQuery needs to run the query at runtime as the capture block is executed. This is called a dynamic query. Dynamic queries are extremely flexible and ExoQuery is very good at handling them however there are a few caveats:

• Dynamic queries require the ExoQuery Query-Compiler to run with your runtime-code. Specifically, wherever you call the .buildFor.SomeDatabase() function.
• It can be problematic to call this code from performance-critical areas because their cost can be in the order of milliseconds (whereas non-dynamic queries have zero runtime cost since they are created inside the compiler). Be sure to test out how much time your dynamic-queries are taking if you have any concerns. Kotlin’s measureTime function is useful for this so long as you run it 5-10 times to let JIT do its work.
• You will not see dynamic-queries in the SQL log output because they are not generated until runtime, although you will see a message that the query is dynamic.

Dynamic queries effectively allow you pass around SqlQuery<T> and SqlExpression<T> objects without any restrictions or limitations. For example:

@SqlDynamic
fun filteredIds(robotsAllowed: Boolean, value: SqlExpression<String>) =
  if (robotsAllowed)
    sql {
      Table<Person>().filter { p -> p.name == value }.map { p -> p.id }
      union
      Table<Robot>().filter { r -> r.model == value }.map { r -> r.id }
    }
  else
    sql { Table<Person>().filter { p -> p.name == value }.map { r -> r.id } }

val q = sql {
  Table<Tenants>().filter { c -> filteredIds(true, sql.expression { c.signatureName }) }
}
//> SELECT c.signatureName, c.rentalCode, c.moveInDate FROM Tenants c WHERE c.signatureName IN (SELECT p.id FROM Person p WHERE p.name = ? UNION SELECT r.id FROM Robot r WHERE r.model = ?)

Note several things:

• The @SqlFragment annotation is used to mark the function as a dynamic function, otherwise a parsing error will occur.
• Captured functions will typically have one or two runtime flags and the other parameters going in should be SqlQuery<T> or SqlExpression<T> objects.
• Notice how above I used capture.expression { c.signatureName } nested inside another capture block. This is required because c.signatureName is merely a String, not a SqlExpression<String> type.

Creating Query Fragments from Collections

Another advantage of dynamic queries is that you can use them to create query-fragments inside of collections. For example, the following code takes a list of possible names and creates a person.name == x || person.name == y || ... set of clauses from the list.

val possibleNames = listOf("Joe", "Jack")

@SqlDynamic
fun joinedClauses(p: SqlExpression<Person>) =
  possibleNames.map { n -> sql.expression { p.use.name == param(n) } }
    .reduce { a, b -> sql.expression { a.use || b.use } }

val filteredPeople = sql {
  Table<Person>().filter { p -> joinedClauses(sql.expression { p }).use }
}

filteredPeople.buildFor.Postgres()
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.name = ? OR p.name = ?

Again, notice how I wrapped p into capture.expression { p } to make it a SqlExpression<Person> type. What is being passed from filteredPeople to joinedClauses is literally the symbolic expression p.