Dynamic SQL Parameters with Anorm and Scala Play Framework

anorm, playframework, playframework-2.0, scala, sql

Solution

The important thing is that you have to create values of type `ParameterValue`. This is normally done using the `toParameterValue()` function.

One way would be to create a sequence of Options that you flatten:

val onList = Seq(
  Some('school_id -> input.school),
  input.rooms.map('rooms -> _),
  input.bathrooms.map('bathrooms -> _)
).flatten

This sequence can then be mapped to correct values:

SQL(
  "SELECT * FROM Houses WHERE " + whereString
).on(
  onList.map(v => v._1 -> toParameterValue(v._2)): _*
)

This can be simplified like this:

val onList = Seq(
  Some('school_id -> input.school),
  input.rooms.map('rooms -> _),
  input.bathrooms.map('bathrooms -> _)
).flatMap(_.map(v => v._1 -> toParameterValue(v._2)))

SQL(
  "SELECT * FROM Houses WHERE " + whereString
).on(
  onList: _*
)

Or maybe the simplest solution would be this:

val onList = Seq(
  Some('school_id -> toParameterValue(input.school)),
  input.rooms.map('rooms -> toParameterValue(_)),
  input.bathrooms.map('bathrooms -> toParameterValue(_))
).flatten

SQL(
  "SELECT * FROM Houses WHERE " + whereString
).on(
  onList: _*
)

Problem

Is it possible to dynamically create a list for anorm's "on" method? I have a form with optional inputs and currently I check each Option and create a list with the defined Options and am trying to pass this through to anorm. Currently I get this compilation error ``` type mismatch; found : List[java.io.Serializable] required: (Any, anorm.ParameterValue[_]) ``` I'm not sure how I would go about creating this list. Current code : ``` val onList = List( 'school_id = input.school, if(input.rooms isDefined) ('rooms -> input.rooms) else "None" , if(input.bathrooms isDefined) ('bathrooms -> input.bathrooms) else "None" , if(input.houseType isDefined) ('houseType -> input.houseType) else "None" , if(input.priceLow isDefined) ('priceLow -> input.priceLow) else "None" , if(input.priceHigh isDefined) ('priceHigh -> input.priceHigh) else "None" , if(input.utilities isDefined) ('utilities -> input.utilities) else "None" ).filter(_!="None") SQL("SELECT * FROM Houses WHERE " + whereString).on(onList).as(sqlToHouse *) ``` I've tried doing this because initially I thought it would be the same as ``` .on('rooms -> input.rooms, 'bathroom -> input.bathrooms... etc) ``` EDIT: Code is now: ``` val onList = Seq( ('school_id -> input.school), if(input.rooms isDefined) ('rooms -> input.rooms.get) else None , if(input.bathrooms isDefined) ('bathrooms -> input.bathrooms.get) else None , if(input.houseType isDefined) ('houseType -> input.houseType.get) else None , if(input.priceLow isDefined) ('priceLow -> input.priceLow.get) else None , if(input.priceHigh isDefined) ('priceHigh -> input.priceHigh.get) else None , if(input.utilities isDefined) ('utilities -> input.utilities.get) else None ).filter(_!=None).asInstanceOf[Seq[(Any,anorm.ParameterValue[_])]] ``` using SQL command: ``` SQL("SELECT * FROM Houses WHERE " + whereString).on(onList:_*).as(sqlToHouse *) ``` Now getting the exception ``` [ClassCastException: java.lang.Integer cannot be cast to anorm.ParameterValue] ```

Original source