-
-
Save cvogt/9193220 to your computer and use it in GitHub Desktop.
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 |
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
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!
I made it work in Slick 3.1.0 like so: