Anorm: WHERE condition, conditionally

问题:

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

回答1:

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.).

回答2:

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.)