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