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?

Dunghill

It looks to me like there is an answer here, but I don't understand what whereString is supposed to be, so I don't fully understand that question. – Paz

Also, similarly, what about dynamic "order by" lists? – Paz

@Robin Green So the whereString would be something like what I posted below: ({countryCode} is null or c.code = {countryCode}) and ({capital} is null or c.capital = {capital}) – Gangster

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.

Gangster

This is nice, but seems to have limitations with some more "exotic" types like ZonedDateTime or LocalDateTime (from java.time) even if those types otherwise work very nicely with Anorm. Trying to use this with optional ZonedDateTime, I got "ERROR: could not determine data type of parameter $1". If anyone wants to give it a shot, try applying this approach to my use case. – Misunderstood

@Misunderstood Didn't try it, but maybe that would work if you pass Option.empty[ZonedDateTime] instead of None? – Gangster

Thing is, it always fails at runtime (with "could not determine data type of parameter") whether the optional ZonedDateTime is present or not (no matter if it is Some(2016-01-09T17:19:25Z[UTC]) or None or Option.empty[ZonedDateTime]). I have a complete test case at GitHub if you want to have a look. – Misunderstood

@Misunderstood That seems to be an issue with the Postgresql driver. It's really ugly, but probably that if you used {created}::timestamptz instead of {created} it would work. – Gangster

Thanks, with ({created}::timestamptz is null or created >= {created}) it indeed works fine! – Misunderstood

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

Spiniferous

This question is about a search (i.e. a SELECT query) not an insertion. Maybe I'm missing something but I can't see how it would work for a SELECT. – Paz

@RobinGreen Hmm then I do not know of anorm feature which does that. Anyways edited it. – Spiniferous

Answer 3

Anorm seems to cleary operate on the premise that you insert plain old SQL, without any features such as typesafety or query building. It probably isn't the right tool for dynamic queries. Constructing queries has to be string manipulation eventually, so I would recommend using a library that does this for you. It should be easy to integrate any library for generating SQL statements with Anorm.

You can dynamically construct a query and pass that query string to Anorm with a library like jOOQ. As a bonus you'll get support for many databases. jOOQ seems to be popular but there probably are many other libraries that you could use instead. Or replace Anorm completely if it isn't suited for your project.

Partain