使用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))，如果例如仅设置了exitLocation，则会导致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屋！