Slick:选项列筛选

Question

我想做这样的事情（这是一个虚构的例子来简化我的实际问题）：

def findByGender(isMale: Option[Boolean]) = {
  People.filter(row => row.name.isNotNull && isMale match {
    case Some(true) => row.wife.isNotNull      // find rows where wife column is not null
    case Some(false) => row.wife.isNull        // find rows where wife column is null
    case None => true                          // select everything
  })    
}

由于最后一个"true"，因此不进行编译。有更好的方法吗？

Answer 1

您必须使其成为Column[Boolean]:

def findByGender(isMale: Option[Boolean]) = {
  People.filter(row => row.name.isNotNull && isMale match {
    case Some(true) => row.wife.isNotNull      // find rows where wife column is not null
    case Some(false) => row.wife.isNull        // find rows where wife column is null
    case None => LiteralColumn(true)           // select everything
  })    
}

Answer 2

如果您使用Slick 3.3.x，您可以使用以下解决方案：

def findByGender(isMale: Option[Boolean]) = 
  People
    .filter(_.name.isDefined)
    .filterIf(isMale == Some(true))(_.wife.isDefined)
    .filterIf(isMale == Some(false))(_.wife.isEmpty)

或

def findByGender(isMale: Option[Boolean]) = 
  People
    .filter(_.name.isDefined)
    .filterOpt(isMale) {
      case (row, true) => row.wife.isDefined
      case (row, false) => row.wife.isEmpty
    }

有3种情况：

1. 定义了isMale并等于Some(true)

   结果SQL:select * from people when (name is not null) and (wife is not null);

2. 定义了isMale并且等于Some(false):

   结果SQL:select * from people when (name is not null) and (wife is null);

3. isMale为空

   结果SQL:select * from people when name is not null;