"In" Clause with Anorm

At Lucidchart, two coding styles have been adopted because Anorm doesn't support the 'in' clause.

1. Code that allows for, or is conducive to, SQL injection.
   
   Example:
   SQL(""" SELECT * FROM users WHERE id IN (%s) """.format(userIds.mkString(",")))
   
   
2. Code that is hard to follow and duplicate.
   
   Example
   val params = List(1, 2, 3) 
   val paramsList = for ( i <- 0 until params.size ) yield ("userId" + 
   i) // results in List("userId0", "userId1", "userId2") 
   SQL(""" SELECT * FROM users WHERE id IN ({%s}) """.format(paramsList.mkString("},{")) // 
   produces "id in ({userId0},{userId1},{userId2})" 
   .on(paramsList.zip(params)) // produces Map("userId0" -> 1, 
   "userId1" -> 2, ...)
   

Ugly much? The madness ends here with code samples.

Now, the code.

We're going to create a class that can be converted to an Anorm SqlQuery object. Super simple.

In models/AnormImplicits.scala

package models

import anorm._

object AnormImplicits {
class RichSQL val query: String, val parameterValues: (Any, ParameterValue[Any])*) {
/**
* Convert this object into an anorm.SqlQuery
*/
def toSQL = SQL(query).on(parameterValues: _*)
/**
* Similar to anorm.SimpleSql.on, but takes lists instead of single values.
* Each list is converted into a set of values, and then passed to anorm's
* on function when toSQL is called.
*/
def onList[A](args: (String, Iterable[A])*)(implicit toParameterValue: (A) => ParameterValue[A]) = {
val condensed = args.map { case (name, values) =>
val search = "{" + name + "}"
val valueNames = values.zipWithIndex.map { case (value, index) => name + "_" + index }
val placeholders = valueNames.map { name => "{" + name + "}" }
val replace = placeholders.mkString(",")
val converted = values.map { value => toParameterValue(value).asInstanceOf[ParameterValue[Any]] }
val parameters = valueNames.zip(converted)
(search, replace, parameters)
}
val newQuery = condensed.foldLeft(query) { case (newQuery, (search, replace, _)) =>
newQuery.replace(search, replace)
}
val newValues = parameterValues ++ condensed.map { case (_, _, parameters) => parameters }.flatten
new RichSQL(newQuery, newValues: _*)
}
}
object RichSQL {
def apply[A](query: String) = new RichSQL(query)
}

}

That's it! Now you can use it in a model.

RichSQL(""" SELECT * FROM users WHERE id IN ({userIds}) """).onList("userIds" -> userIds).toSQL.as(userParser *)(connection)

Benefits

1. Easy to read and use
2. Works flawlessly with Anorm
3. Prevents SQL Injection
4. Works with all types of values - strings, longs, ints, uuids, binary, etc.