Anorm: WHERE condition, conditionally

Question

Consider a repository/DAO method like this, which works great:

def countReports(customerId: Long, createdSince: ZonedDateTime) =
  DB.withConnection {
    implicit c =>
      SQL"""SELECT COUNT(*)
            FROM report
            WHERE customer_id = $customerId
            AND created >= $createdSince
         """.as(scalar[Int].single)
  }

But what if the method is defined with optional parameters:

def countReports(customerId: Option[Long], createdSince: Option[ZonedDateTime])

Point being, if either optional argument is present, use it in filtering the results (as shown above), and otherwise (in case it is None) simply leave out the corresponding WHERE condition.

What's the simplest way to write this method with optional WHERE conditions? As Anorm newbie I was struggling to find an example of this, but I suppose there must be some sensible way to do it (that is, without duplicating the SQL for each combination of present/missing arguments).

Note that the java.time.ZonedDateTime instance maps perfectly and automatically into Postgres timestamptz when used inside the Anorm SQL call. (Trying to extract the WHERE condition as a string, outside SQL, created with normal string interpolation did not work; toString produces a representation not understood by the database.)

Play 2.4.4

Disclaimer

I've played with different options at a GitHub test repo; feel free to fork. – Disclaimer

Answer 1

Finally got this simpler approach posted by Joel Arnold to work in my example case, also with ZonedDateTime!

def countReports(customerId: Option[Long], createdSince: Option[ZonedDateTime]) =
  DB.withConnection {
    implicit c =>
      SQL( """
          SELECT count(*) FROM report
          WHERE ({customerId} is null or customer_id = {customerId})
          AND ({created}::timestamptz is null or created >= {created})
           """)
        .on('customerId -> customerId, 'created -> createdSince)
        .as(scalar[Int].singleOpt).getOrElse(0)
  }

The tricky part is having to use {created}::timestamptz in the null check. As Joel commented, this is needed to work around a PostgreSQL driver issue.

Apparently the cast is needed only for timestamp types, and the simpler way ({customerId} is null) works with everything else. Also, comment if you know whether other databases require something like this, or if this is a Postgres-only peculiarity.

(While wwkudu's approach also works fine, this definitely is cleaner, as you can see comparing them side to side in a full example.)

Disclaimer

Answer 2

One approach is to set up filter clauses such as

val customerClause =
  if (customerId.isEmpty) ""
  else " and customer_id={customerId}"

then substitute these into you SQL:

SQL(s"""
  select count(*)
    from report
    where true
      $customerClause
      $createdClause
""")
.on('customerId -> customerId, 
  'createdSince -> createdSince)
.as(scalar[Int].singleOpt).getOrElse(0)

Using {variable} as opposed to $variable is I think preferable as it reduces the risk of SQL injection attacks where someone potentially calls your method with a malicious string. Anorm doesn't mind if you have additional symbols that aren't referenced in the SQL (i.e. if a clause string is empty). Lastly, depending on the database(?), a count might return no rows, so I use singleOpt rather than single.

I'm curious as to what other answers you receive.

Edit: Anorm interpolation (i.e. SQL"...", an interpolation implementation beyond Scala's s"...", f"..." and raw"...") was introduced to allow the use $variable as equivalent to {variable} with .on. And from Play 2.4, Scala and Anorm interpolation can be mixed using $ for Anorm (SQL parameter/variable) and #$ for Scala (plain string). And indeed this works well, as long as the Scala interpolated string does not contains references to an SQL parameter. The only way, in 2.4.4, I could find to use a variable in an Scala interpolated string when using Anorm interpolation, was:

val limitClause = if (nameFilter="") "" else s"where name>'$nameFilter'"
SQL"select * from tab #$limitClause order by name"

But this is vulnerable to SQL injection (e.g. a string like it's will cause a runtime syntax exception). So, in the case of variables inside interpolated strings, it seems it is necessary to use the "traditional" .on approach with only Scala interpolation:

val limitClause = if (nameFilter="") "" else "where name>{nameFilter}"
SQL(s"select * from tab $limitClause order by name").on('limitClause -> limitClause)

Perhaps in the future Anorm interpolation could be extended to parse the interpolated string for variables?

Edit2: I'm finding there are some tables where the number of attributes that might or might not be included in the query changes from time to time. For these cases I'm defining a context class, e.g. CustomerContext. In this case class there are lazy vals for the different clauses that affect the sql. Callers of the sql method must supply a CustomerContext, and the sql will then have inclusions such as ${context.createdClause} and so on. This helps give a consistency, as I end up using the context in other places (such as total record count for paging, etc.).

Marhtamari

There is no more risk of SQL injection by using Anorm interpolation SQL"..." (which is not standard string interpolation and) which set the parameter values in the same way – Leathery

Thanks, this approach is pretty clean, and it does work! What I initially missed was that you're using SQL(""" """) instead of SQL""" """. These are NOT the same, as I learned the hard way after fighting with ERROR: syntax error at or near "{" for some time. You can't use the {customerId} syntax inside SQL""" """. – Disclaimer

@cchantep: For this "conditional condition" case, to me it seems that I need the {customerId} and .on() approach to get it working. You can prove me wrong, if you like, by posting an answer that's equivalent but implemented with SQL"..." :) – Disclaimer

(And of course I meant SQL(s""" """) above—the s is required to get $customerClause etc properly replaced.) – Disclaimer

Interesting - I had not seen the SQL "..." format. Tks for that. – Marhtamari

Seems there are some 2.3/2.4 differences - just reading about #$ for example. – Marhtamari

#$ allows to mix string interpolation inside Anorm interpolation, safely. – Leathery

I found a potentially cleaner solution, but couldn't get it working with ZonedDateTime (no problem with Strings and Longs): https://mcmap.net/q/869140/-anorm-dynamic-filters – Disclaimer

@Jonik, those questions seems to address shortening of the on clauses. – Marhtamari

The linked answer is essentially about the same thing as what I'm looking for, but as noted, I couldn't get it to work with ZonedDateTime. (If my question only had customerId: Long then that solution would work fine.) I have a GitHub repo with full test case if you wanna try it out. – Disclaimer