Slick: Dynamic query conditions using the **MaybeFilter** (Updated to support nullable columns)

gistfile1.scala

import scala.slick.lifted.CanBeQueryCondition
// optionally filter on a column with a supplied predicate
case class MaybeFilter[X, Y](val query: scala.slick.lifted.Query[X, Y]) {
  def filter[T,R:CanBeQueryCondition](data: Option[T])(f: T => X => R) = {
    data.map(v => MaybeFilter(query.filter(f(v)))).getOrElse(this)
  }
}

// example use case
import java.sql.Date
class SomeTable(tag: Tag) extends Table[(Int, Date, Date)](tag, "some_table") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def created = column[Date]("created")
  def modified = column[Date]("modified")
  def * = (id, created, modified)
}
lazy val someTable = TableQuery[SomeTable]
someTable.ddl.create

def find(id: Option[Int], createdMin: Option[Date], createdMax: Option[Date], modifiedMin: Option[Date], modifiedMax: Option[Date]) = {
  MaybeFilter(someTable)
    .filter(id)(v => d => d.id === v)
    .filter(createdMin)(v => d => d.created >= v)
    .filter(createdMax)(v => d => d.created <= v)
    .filter(modifiedMin)(v => d => d.modified >= v)
    .filter(modifiedMax)(v => d => d.modified <= v)           
    .query
}
find(Some(1),None,None,None,None).list // <- example: find by id only

Very helpful, thank!

This is interesting.

You can also check my alternative here if you want to:
http://stackoverflow.com/questions/28281232/slick-create-query-conjunctions-disjunctions-dynamically/28303670#28303670

I used it like this for slick 2.0

implicit class OptionFilter[ X, Y ]( query: Query[ X, Y, Seq ] ) {
  def filteredBy[ T ]( op: Option[ T ] )( f: ( X, T ) => Column[ Option[ Boolean ] ] ): Query[ X, Y, Seq ] = {
    op map { o => query.filter( f( _, o ) ) } getOrElse { query }
  }

  def foundBy[ T ]( op: Option[ T ] )( f: ( X, T ) => Column[ Option[ Boolean ] ] ): Query[ X, Y, Seq ] = {
    op map { o => query.filter( f( _, o ) ) } getOrElse { query.take( 0 ) }
  }
}

So you can add it to the query methods. FilteredBy works for filtering lists and it returns the query if no filters were applied. FoundBy works for finding a single register, if no filter defined it returns None.
Here is the gist https://gist.github.com/leandrob13/09c6bfb333e106cf5931

Does anyone know if there's a version of this which works with Slick 3.x?

You save my life!! 😊

@rossanthony Did you end up finding anything?

I made it work in Slick 3.1.0 like so:

case class MaybeFilter[X, Y, C[_]](val query: slick.lifted.Query[X, Y, C]) {
  def filter[T,R:CanBeQueryCondition](data: Option[T])(f: T => X => R) = {
    data.map(v => MaybeFilter(query.withFilter(f(v)))).getOrElse(this)
  }
}

This works fine if your doing something simple but doesn't work if you want to use for example

.filter(ids)(v => d => d.created inset v)   where ids is Option[List[String]]

or in the case of a compound query like this

.filter(searchTerm)(v => d => (d.title like v || d.keywords like v))

is there a way to handle more specific cases like this using the maybeFilter?

I came across this when trying to find a new solution for this

https://github.com/pvoznenko/play-slick-angular-test-example/blob/master/app/models/ReportsModel.scala

Hi...
Thanks for this info.... Can I ask if this approach works with compiled queries as well..

We use an implicit class to make a nice api around a maybeFilter:

implicit class QueryOps[+E, U](query: Query[E, U, Seq]) {
    def resultHead(onEmpty: Throwable)(implicit ec: ExecutionContext): DBIO[U] =
      DBIOOptionOps(query.take(1).result.headOption).failIfNone(onEmpty)

    def maybeFilter(f: E => Rep[Option[Boolean]]): Query[E, U, Seq] =
      query.withFilter { (e: E) =>
        f(e).getOrElse(true)
      }
  }

This is how you'd use it:

test("maybeFilter uses filter if condition is defined") {
    val f = for {
      _ <- db.run(books += Book(30, "Some book", Option("30 some code")))
      result <- db.run(books.maybeFilter(_.id === Option(30l)).result)
    } yield result

    f.futureValue.length shouldBe 1
    f.futureValue.head.id shouldBe 30l
  }

  test("maybeFilter ignores filter if condition is None") {
    val f = for {
      _ <- db.run(books += Book(40, "Some book"))
      result <- db.run(books.maybeFilter(_.id === Option.empty[Long]).result)
    } yield result

    f.futureValue.length shouldBe >(1)
    f.futureValue.map(_.id) should contain(40l)
  }

More info:

https://github.com/advancedtelematic/libats/blob/master/libats-slick/src/main/scala/com/advancedtelematic/libats/slick/db/SlickExtensions.scala
https://0io.eu/blog/2017/09/15/useful-slick-implicits

@simao, this fails on:

table.maybeFilter(_.someColumn.asColumnOf[String] like arg.map(a => s"%$a%)).take(10).result

at least on mariadb it looks like it generates invalid query in the end:

Query is: select `id`, `some_column`, `status` from `test_table` where ifnull((convert(`some_column`,VARCHAR)) like '%test%',true) limit 10

Do you know what is this ifnull? - update: ifnull is db function to filter expressions

without maybeFilter it does not work either, but that is another story :)

update: problem is related to slick, as it can't do like query on field with custom mapping. once column type is plain String, it all works

how to make it happen on Slick 1.0.0?

Very useful, thank you!