Slick: Option column filtering

Question

I want to do something like this (this is a made-up example to simplify my actual problem):

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

This does not compile because of the last "true". Any better way to do this?

score 4 · Accepted Answer · answered Oct 24 '14 at 20:02

4

You have to make it a 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
  })    
}

answered Oct 24 '14 at 20:02

How did you find this? I can't even find any mention of `LiteralColumn` [here](http://slick.typesafe.com/doc/2.1.0/search.html?q=LiteralColumn&check_keywords=yes&area=default) – pathikrit Oct 27 '14 at 08:45
No idea where I found it. Was a very long time ago. – Oct 27 '14 at 08:48

Alex Elkin · Answer 2 · 2019-09-05T11:34:59.470

If you're using Slick 3.3.x you can use the following solution:

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

or

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

There are 3 cases:

isMale is defined and equal Some(true)

Result SQL: select * from people when (name is not null) and (wife is not null);
isMale is defined and equal Some(false):

Result SQL: select * from people when (name is not null) and (wife is null);
isMale is empty

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

Slick: Option column filtering

2 Answers2

Linked