scalagroup-byslick

Conditionally map a nullable field to None value in Slick


In Slick 2.1, I need to perform a query/map operation where I convert a nullable field to None if it contains a certain non-null value. Not sure whether it matters or not, but in my case the column type in question is a mapped column type. Here is a code snippet which tries to illustrate what I'm trying to do. It won't compile, as the compiler doesn't like the None.

case class Record(field1: Int, field2: Int, field3: MyEnum)

sealed trait MyEnum
val MyValue: MyEnum = new MyEnum { }

// table is a TableQuery[Record]
table.map { r => (
  r.field1,
  r.field2,
  Case If (r.field3 === MyValue) Then MyValue Else None // compile error on 'None'
  )
}

The error is something like this:

type mismatch; found : None.type required: scala.slick.lifted.Column[MyEnum]

Actually, the reason I want to do this is that I want to perform a groupBy in which I count the number of records whose field3 contains a given value. I couldn't get the more complicated groupBy expression working, so I backed off to this simpler example which I still can't get working. If there's a more direct way to show me the groupBy expression, that would be fine too. Thanks!

Update

I tried the code suggested by @cvogt but this produces a compile error. Here is a SSCCE in case anyone can spot what I'm doing wrong here. Compile fails with "value ? is not a member of Int":

import scala.slick.jdbc.JdbcBackend.Database
import scala.slick.driver.H2Driver

object ExpMain extends App {

  val dbName = "mydb"
  val db = Database.forURL(s"jdbc:h2:mem:${dbName};DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")
  val driver = H2Driver

  import driver.simple._

  class Exp(tag: Tag) extends Table[(Int, Option[Int])](tag, "EXP") {
    def id = column[Int]("ID", O.PrimaryKey)
    def value = column[Option[Int]]("VALUE")
    def * = (id, value)
  }
  val exp = TableQuery[Exp]

  db withSession { implicit session =>
    exp.ddl.create

    exp += (1, (Some(1)))
    exp += (2, None)
    exp += (3, (Some(4)))

    exp.map { record =>
      Case If (record.value === 1) Then 1.? Else None  // this will NOT compile
      //Case If (record.value === 1) Then Some(1) Else None  // this will NOT compile
      //Case If (record.value === 1) Then 1 Else 0  // this will compile
    }.foreach {
      println
    }
  }

}

Solution

  • I need to perform a query/map operation where I convert a nullable field to None if it contains a certain non-null value

    Given the example data you have in the update, and pretending that 1 is the "certain" value you care about, I believe this is the output you expect:

    None, None, Some(4)
    

    (for rows with IDs 1, 2 and 3)

    If I've understood the problem correctly, is this what you need...?

    val q: Query[Column[Option[Int]], Option[Int], Seq] = exp.map { record => 
      Case If (record.value === 1) Then (None: Option[Int]) Else (record.value)
    }
    

    ...which equates to:

    select (case when ("VALUE" = 1) then null else "VALUE" end) from "EXP"