SLICK: A simple query using union and parameter lists

Question

I'm new to SLICK (2.1) and am lost in creating my first query using union. Because the parameters are provided from external (via a web interface) eventually, I set them as optional. Please see the comment in the code below. How to create an appropriate query?

My actual class is more complex which I simplified for the sake of this question.

case class MyStuff(id: Int, value: Int, text: String)

class MyTable (tag: Tag) extends Table[MyStuff](tag, "MYSTUFF"){

  def id = column[Int]("ID", O NotNull)
  def value = column[Int]("VALUE", O NotNull)
  def text = column[String]("TEXT", O NotNull)

  def * = 
  (id, 
  value, 
  text).shaped <> ((MyStuff.apply _).tupled, MyStuff.unapply)
}

object myTable extends TableQuery(new MyTable(_)){
  def getStuff(ids: Option[List[Int]], values: Option[List[Int]])(implicit session: Session): Option[List[MyStuff]] = {
    /*
    1) If 'ids' are given, retrieve all matching entries, if any.
    2) If 'values' are given, retrieve all matching entries (if any), union with the results of the previous step, and remove duplicate entries.
    4) If neither 'ids' nor 'values' are given, retrieve all entries.
    */
  }
}

getStuff is called like this:

db: Database withSession { implicit session => val myStuff = myTable.getStuff(...) }

O NotNull should not be written explicitly. This is the default in Slick. — cvogt, May 05 '15 at 14:34

score 2 · Accepted Answer · answered May 05 '15 at 14:46

You can use inset if the values are Some, otherwise a literal false and only filter when something is not None.

  if(ids.isDefined || values.isDefined)
    myTable.filter(row =>
      ids.map(row.id inSet _).getOrElse(slick.lifted.LiteralColumn(false))
    ) union myTable.filter(row =>
      values.map(row.value inSet _).getOrElse(slick.lifted.LiteralColumn(false))
    )
  else myTable

score 0 · Answer 2 · answered Mar 20 '15 at 14:06

0

If I understand you correctly you want to build a filter at runtime from the given input. You can look at the extended docs for 3.0 (http://slick.typesafe.com/doc/3.0.0-RC1/queries.html#sorting-and-filtering) at "building criteria using a "dynamic filter" e.g. from a webform". This part of the docs is also valid for version 2.1.

answered Mar 20 '15 at 14:06

tfh

620
1
4
14

From the linked documentation I understand my query should look something like this: def getStuff(ids: Option[List[Int]], values: Option[List[Int]]) = myTable.filter { myTable => List( ids.map(myTable.id === _), values.map(myTable.value === _) ).distinct } Which fails with various errors ( inferred type arguments , type mismatch, Type T cannot be a query condition). – jans Apr 30 '15 at 08:48

SLICK: A simple query using union and parameter lists

2 Answers2