anorm dynamic filters

Question

I'm studying a little the anorm documentation (from play framework) and is not clear if it supports a common query use case: dynamic filters, that is the user fills in 2 or 3 search criteria on a 10 fields search form.

In this case how can I dynamically construct the query without the classic string manipulation?

Answer 1

Yes, I think the question referenced by Robin Green contains the answer. Just define your query with all the possible criteria using placeholders (e.g. {criterion1}) and call the on() method on the query, passing the actual Seq of Option parameters as described in the accepted answer.

---

Modified example from the Anorm doc, assuming you have two criteria but only want your query to filter on the country code and not on the capital:

SQL(
"""
select * from Country c 
join CountryLanguage l on l.CountryCode = c.Code 
where ({countryCode} is null or c.code = {countryCode})
  and ({capital} is null or c.capital = {capital});
"""
).on("countryCode" -> Some("FRA"), "capital" -> None)

That should do the trick.

Answer 2

Short answer first. Suppose say you have a table in database containing 3 columns: name, email, pass. But from the user, you only got name and password but not email. So make Options of all 3

val name:Option[String] = Some("alice")
val email:Option[String] = None
val pass:Option[String] = Some("password")

//For db insertion have this:

  DB.withConnection { implicit conn =>
  SQL("INSERT INTO USERS (name,email,pass) VALUES ({n},{e},{p})").on(
 'n -> name, 'e -> email,'p -> pass).executeInsert()
 }

Doing the above, as email is None, it will insert null in your database. So in your case, for all your 10 columns, you can define them in the SQL statement above and pass Option in on(). If any of them is None, then it will take it as null in database.

Though there can be a issue if there is a constraint on a column in your schema as NOT NULL. In which case you can use getOrElse for such columns asbelow:

DB.withConnection { implicit conn =>
  SQL("INSERT INTO USERS (name,email,pass) VALUES ({n},{e},{p})").on(
 'n -> name, 'e -> email.getOrElse("Default Email"),'p -> pass).executeInsert()

The below is a comprehend list on how play converts the type to database type. It can be found in object anorm.ToStatement:

        case Some(bd: java.math.BigDecimal) => stmt.setBigDecimal(index, bd)
        case Some(o) => stmt.setObject(index, o)
        case None => stmt.setObject(index, null)
        case bd: java.math.BigDecimal => stmt.setBigDecimal(index, bd)
        case date: java.util.Date => stmt.setTimestamp(index, new java.sql.Timestamp(date.getTime()))
        case o => stmt.setObject(index, o)

Above you see, for None it takes it as null.

---

In case of SELECT hmm, I am not aware of any anorm feature which helps here, but I guess simple String manipulation might suffice:

def getColumns(xs:List[Option[_]]):String = {
    val notNone = xs.collect{
       case Some(x) => x.toString
    }
    notNone.mkString(",")
}

And then SQL("SELECT %s from table".format(getColumns(List(nameColumn,emailColumn,passColumn))).

Though this is not what you want. Anorm is just a sql building library. To do what you want, it will also have to remember your table schema (i.e. atleast column names..). I do not think anorm is made to do all that