Dynamic SQL Parameters with Anorm and Scala Play Framework

Question

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]

How is this supposed to work? What does whereString look like? — Robin Green
– Robin Green, Commented Aug 14, 2013 at 16:42

maba · Accepted Answer · 2013-03-25 16:14:58Z

12

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: _*
)

edited Mar 25, 2013 at 16:14

answered Mar 25, 2013 at 14:49

maba

48.3k10 gold badges114 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

damian Over a year ago

That's great! I didn't know about the toParameterValue function. Thanks

damian · Accepted Answer · 2013-03-23 23:53:26Z

1

So I ended up just calling on multiple times.

var query = SQL("SELECT * FROM Houses WHERE " + whereString).on('school_id -> input.school)
if(input.rooms isDefined)       query= query.on('rooms -> input.rooms.get)
if(input.bathrooms isDefined)   query= query.on('bathrooms -> input.bathrooms.get)
if(input.houseType isDefined)   query= query.on('houseType -> input.houseType.get)
if(input.priceLow isDefined)    query= query.on('priceLow -> input.priceLow.get)
if(input.priceHigh isDefined)   query= query.on('priceHigh -> input.priceHigh.get)
if(input.utilities isDefined)   query= query.on('utilities -> input.utilities.get)
query.as(sqlToHouse *)

answered Mar 23, 2013 at 23:53

damian

1,4191 gold badge22 silver badges42 bronze badges

Comments

cchantep · Accepted Answer · 2014-05-08 10:47:57Z

1

You can have a look at multivalue parameter is next Anorm (coming Play 2.3/master).

answered May 8, 2014 at 10:47

cchantep

9,1763 gold badges32 silver badges43 bronze badges

Collectives™ on Stack Overflow

Dynamic SQL Parameters with Anorm and Scala Play Framework

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related