Query Expansion

.expand – List Parameter Insertion

As of 1.6, the preferred way to write queries is string interpolation.

Relate allows collections of data to be inserted into queries with the expand method. This method must be called before the on method and, like on, takes an anonymous function as a parameter. The anonymous function denotes how expand the original query.

.commaSeparated

To take advantage of the SQL IN clause, use the commaSeparated method. This method expands the query by creating a comma separated list. It takes two parameters:

1. The name of the parameter to expand
2. The size of the comma separated list

Later, when calling the on method, use the plural versions of the insertion methods (just add an 's') to insert the collection into the query. Here is an example of its use:

import com.lucidchart.open.relate._
import com.lucidchart.open.relate.Query._

val ids = (0 until 150 by 10).map { i => i.toLong }

SQL("""
  SELECT *
  FROM pokedex
  WHERE id IN ({ids})
""").expand { implicit query =>
  commaSeparated("ids", ids.size)
}.on { implicit query =>
  longs("ids", ids)
}

Once again, importing com.lucidchart.open.relate.Query._ and declaring the query object as implicit allows implicit parameter insertion method calls.

.tupled and .onTuples

To insert multiple records into a table with one query, use the tupled and onTuples methods. tupled should be called in the function passed to expand. It specifies the name of the parameter to be replaced with tuples and the number of tuples that will be inserted. The method takes three parameters:

1. The name of the parameter to expand
2. A collection that contains the parameter name for each position in the tuple
3. The number of tuples to insert

After the expand method has been called, the onTuples method inserts values for the tuples in the query. It takes three parameters:

1. The name of the parameter for which to insert values
2. A collection of tuple data
3. An anonymous function that inserts data into tuples

onTuples will iterate through the collection, passing each value into the anonymous function. The anonymous function is also provided with a tuple version of the query object that contains the same insertion methods as the regular query object. However, there is no way to implicitly call methods on the tuple query, so they must be called explicitly. Here's an example of how the process works:

case class Trainer(name: String, catchPhrase: String)

val route1Trainers = List(
  Trainer("Youngster Jimmy", "My Rattata loves berries."),
  Trainer("Lass Haley", "If you make eye contact, you have to battle!"),
  Trainer("Pokemon Breeder Rocco", "Soar high, my little Pidgey!")
)

SQL("""
  INSERT INTO trainers
  VALUES {tuples}
""").expand { implicit query =>
  tupled("tuples", List("name", "catchPhrase"), route1Trainers.size)
}.onTuples("tuples", route1Trainers) { (trainer, query) =>
  query.string("name", trainer.name)
  query.string("catchPhrase", trainer.catchPhrase)
}.executeUpdate()(connection)