CODEWITHSUNDEEP
scalagroup-byslick
JimN
JimN

Reputation: 3150

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
    }
  }

}

Upvotes: 2

Views: 1509

Answers (2)

Richard Dallaway
Richard Dallaway

Reputation: 4330

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"

Upvotes: 4

cvogt
cvogt

Reputation: 11270

You need to wrap MyValue in an Option, so that both outcomes of the conditional are options. In Slick 2.1 you use the .? operator for that. In Slick 3.0 it will likely be Rep.Some(...).

Try

Case If (r.field3 === MyValue) Then MyValue.? Else None

or

Case If (r.field3 === MyValue) Then MyValue.? Else (None: Option[MyEnum])

Upvotes: 1

Related Questions