I was having trouble comparing dates in Scala Slick. It turned out to be human error.

There are a lot of posts on Stack Overflow about the difficulty of comparing date types (such as Joda.date.DateTime) in Scala Slick. However, I was using Java.sql.Date, which should be supported directly by Slick, so these discussions turned out to be red herrings.

The upshot is that there is a big difference between these two lines:

      query1.filter(test => test.startDate <= dt && test.endDate >= dt) // OK

      query1.filter(test => dt >= test.startDate && dt <= test.endDate) // NOPE

In the first, test.startDate is a Date wrapped in Slick's Rep container, which has the inequality operators defined. Then dt gets implicitly lifted into a Rep[Date] as well. However, in the second, because the order was reversed, Scala attempts to find the inequality operators on dt which is a plain Date, doesn't find them, and returns an error.

This will happen with any kind of type, not just Dates. However, the reason it came up with Dates was because I was trying to reorder the comparisons to be more intuitive.

The full file for reference:

package models

import java.sql.Date

import model.DbConfig
import slick.driver.MySQLDriver.api._

import scala.concurrent.Future

/**
  * ericdoi 2016-10-13
  */
case class Test(testId: Int, siteId: Int, modelType: ModelTypeEnum, startDate: Date, endDate: Date)

class TestTableDef(tag: Tag) extends Table[Test](tag, "tests") {
  import ModelTypeMapper._

  def testId = column[Int]("test_id", O.PrimaryKey, O.AutoInc)
  def siteId = column[Int]("site_id")
  def modelType = column[ModelTypeEnum]("model_type")
  def startDate = column[Date]("start_date")
  def endDate = column[Date]("end_date")

  override def * =
    (testId, siteId, modelType, startDate, endDate) <> (Test.tupled, Test.unapply)
}

object Tests extends DbConfig {

  val tests = TableQuery[TestTableDef]

  /** Add new test record and return the record with the auto-incremented id */
  def add(test: Test): Future[Test] = {
    val insertQuery = tests returning tests.map(_.testId) into ((test, testId) => test.copy(testId = testId))

    dbConfig.db.run(insertQuery += test)
  }

  def delete(id: Int): Future[Int] = {
    dbConfig.db.run(tests.filter(_.testId === id).delete)
  }

  def get(id: Int): Future[Option[Test]] = {
    dbConfig.db.run(tests.filter(_.testId === id).result.headOption)
  }

  def listAll: Future[Seq[Test]] = {
    dbConfig.db.run(tests.result)
  }

  def listAllForSiteAndDate(siteId: Option[Int], date: Option[Date]): Future[Seq[Test]] = {

    val query1 = siteId.map { sid =>
      tests.filter(_.siteId === sid)
    }.getOrElse(tests)

    val query2 = date.map { dt =>
      query1.filter(test => test.startDate <= dt && test.endDate >= dt) // OK
      //query1.filter(test => dt >= test.startDate && dt <= test.endDate) // NOPE
    }.getOrElse(query1)

    dbConfig.db.run(query2.result)
  }
}