Anorm:有条件的，有条件的 [英] Anorm: WHERE condition, conditionally

问题描述

考虑这样的存储库/DAO方法，效果很好:

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

如果存在任一可选参数，则将其用于过滤结果(如上所示)，否则(如果为None)只需忽略相应的WHERE条件即可.

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.

使用可选的WHERE条件编写此方法的最简单方法是什么?作为Anorm新手，我一直在努力寻找一个示例，但我想必须有 some 做到这一点的明智方法(也就是说，对于存在/缺失参数的每个组合，都无需复制SQL).

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

请注意，在Anorm SQL调用中使用java.time.ZonedDateTime实例时，它可以完美地自动映射到Postgres timestamptz中. (尝试将WHERE条件提取为使用常规字符串插值创建的SQL外部的字符串是行不通的； toString会生成数据库无法理解的表示形式.)

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

播放2.4.4

推荐答案

最后得到了这个 Joel Arnold发布的简单方法 用于我的示例案例，也可以与ZonedDateTime一起使用！

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

棘手的部分是必须在空检查中使用{created}::timestamptz.正如 Joel所说的一样，这是解决

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.

显然，只有 timestamp 类型才需要强制类型转换，而更简单的方法({customerId} is null)可以与其他所有类型一起使用.另外，如果您知道其他数据库是否需要类似的内容，或者这仅是Postgres的特殊性，请发表评论.

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.

(尽管 wwkudu的方法也可以很好地工作，但这绝对是更清洁的方法，如您所见

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

这篇关于Anorm:有条件的，有条件的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！