Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Slick: Dynamic query conditions using the **MaybeFilter** (Updated to support nullable columns)
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
@ghost

This comment has been minimized.

Copy link

ghost commented May 7, 2014

Very helpful, thank!

@slorber

This comment has been minimized.

Copy link

slorber commented Feb 3, 2015

@leandrob13

This comment has been minimized.

Copy link

leandrob13 commented Sep 25, 2015

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

@rossanthony

This comment has been minimized.

Copy link

rossanthony commented Mar 27, 2016

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

@karellen-kim

This comment has been minimized.

Copy link

karellen-kim commented May 4, 2016

You save my life!! 😊

@rmccullagh

This comment has been minimized.

Copy link

rmccullagh commented Apr 10, 2017

@rossanthony Did you end up finding anything?

@orangeblock

This comment has been minimized.

Copy link

orangeblock commented May 3, 2017

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

This comment has been minimized.

Copy link

ergomesh commented Jun 12, 2017

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

@jaina00

This comment has been minimized.

Copy link

jaina00 commented Oct 18, 2017

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

@simao

This comment has been minimized.

Copy link

simao commented Oct 24, 2017

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

@muradm

This comment has been minimized.

Copy link

muradm commented Nov 12, 2017

@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

@jbarros35

This comment has been minimized.

Copy link

jbarros35 commented Dec 7, 2017

how to make it happen on Slick 1.0.0?

@ErunamoJAZZ

This comment has been minimized.

Copy link

ErunamoJAZZ commented Mar 10, 2018

Very useful, thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.