使用 Slick 3 的带有可选 where 子句的动态查询 [英] Dynamic query with optional where clauses using Slick 3

问题描述

我正在尝试实现一种方法来返回过滤结果，该方法基于一组可能设置也可能未设置的参数.似乎不可能有条件地链接多个过滤器，即从一个过滤器开始......

I'm trying to implement a method to return filtered results, based on a set of parameters which may or may not be set. It doesn't seem like chaining multiple filters is possible conditionally, i.e. starting off with one filter...

val slickFlights = TableQuery[Flights]
val query = slickFlights.filter(_.departureLocation === params("departureLocation").toString)

有条件地向查询添加另一个过滤器(如果它存在于参数映射中)似乎不起作用...

Conditionally adding another filter to the query (if it exists in the Map of params) doesn't seem to work...

if (params.contains("arrivalLocation")) {
      query.filter(_.arrivalLocation === params("arrivalLocation").toString)
}

可以通过其他方式使用 Slick 完成这种条件过滤吗?

Can this sort of conditional filtering be done using Slick through other means?

我遇到过MaybeFilter:https://gist.github.com/cvogt/9193220，这似乎是处理此问题的不错方法.但是它似乎不适用于 Slick 3.x

I've come across the MaybeFilter: https://gist.github.com/cvogt/9193220, which seems to be a decent approach for handling exactly this. However it doesn't seem to work with Slick 3.x

按照以下 Hüseyin 的建议，我还尝试了以下操作:

Following Hüseyin's suggestions below, I have also tried the following:

def search(departureLocation: Option[String], arrivalLocation: Option[String]) = {
    val query = slickFlights.filter(flight =>
       departureLocation.map {
          param => param === flight.departureLocation
       })

其中 slickFlights 是一个 TableQuery 对象 val slickFlights = TableQuery[Flights].但是，这会产生以下编译错误:

Where slickFlights is a TableQuery object val slickFlights = TableQuery[Flights]. However this produces the following compilation error:

value === is not a member of String

Intellij 还抱怨 === 是一个未知符号.也不适用于 ==.

Intellij also complains about the === being an unknown symbol. Doesn't work with == either.

推荐答案

为了让其他尝试在 Slick 中使用可选过滤器的人受益，请查看此处的答案:正确使用光滑过滤器.我终于设法让它与以下一起工作:

For the benefit of anyone else trying to get optional filters working in Slick, have a look at the answer here: right usage of slick filter. I finally managed to get it working with the following:

def search(departureLocation: Option[String], arrivalLocation: Option[String]) = {
  val query = for {
    flight <- slickFlights.filter(f =>
       departureLocation.map(d => 
         f.departureLocation === d).getOrElse(slick.lifted.LiteralColumn(true)) && 
       arrivalLocation.map(a => 
         f.arrivalLocation === a).getOrElse(slick.lifted.LiteralColumn(true))
    )
  } yield flight

关键位是地图末尾的 .getOrElse(slick.lifted.LiteralColumn(true))，这会导致 Slick 按如下方式呈现 SQL，例如，如果仅设置了离开位置...

The key bit being the .getOrElse(slick.lifted.LiteralColumn(true)) on the end of the map, which causes Slick to render SQL as follows if for example only the departureLocation is set...

select * from `flight` 
where (`departureLocation` = 'JFK') and true

而没有它，SQL 看起来像...

whereas without it the SQL looked like...

select * from `flight` 
where (`departureLocation` = 'JFK') and (`arrivalLocation` = '')

这显然意味着它没有行返回.

which obviously meant that it came back with no rows.

这篇关于使用 Slick 3 的带有可选 where 子句的动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！