While playing with Play Framework, Slick and Scala recently, I came across this problem when doing a left join:
[error] SlickException: Read NULL value for column ORDERS.GUEST_ID (TypeMapper.scala:158) [error] models.BarTables$$anonfun$currentTableStateFull$1.apply(BarTable.scala:108)Let's see what happens here:
for{
(guests,orders) <- Guests leftJoin Orders on (_.id === _.guestId)
} yield (guests,orders).list
So this is how you do a left join with Slick. Here's the two tables used in the query:
object Orders extends Table[Order]("ORDERS") with CRUDModel[Order] with Logger{
def id = column[Long]("ID", O.PrimaryKey, O AutoInc) // This is the primary key column
def guestId = column[Long]("GUEST_ID")
def created = column[DateTime]("CREATED")
def delivered = column[Option[DateTime]]("DELIVERED")
def paid = column[Option[DateTime]]("PAID")
def * = id.? ~ guestId ~ created ~ delivered ~ paid <> (Order.apply _, Order.unapply _)
...
object Guests extends Table[User]("GUESTS") with CRUDModel[User]{
def id = column[Long]("ID", O.PrimaryKey, O AutoInc) // This is the primary key column
def name = column[String]("NAME")
def email = column[String]("EMAIL")
def * = id.? ~ name ~ email <> (User.apply _, User.unapply _)
...
That looks quite normal I guess.
But then the problem arises when there's no orders belonging to the guests, and so all the columns selected from the Orders table are null. And guestId cannot be null, it's not defined as an Option (obviously).
What am I doing wrong?
The problem is that the default projection * is used when yielding the result:
yield (guests,orders).list
We have to provide a projection that doesn't use the default one. So after some googling and stackoverflowing, I came across this post, and then this one and the result looks like this:
def maybe = id.? ~ guestId.? ~ created.? ~ delivered ~ paid <> (
tupleToOrder _,
(order: Option[Order]) => None
)
def tupleToOrder(orderTuple: (Option[Long],Option[Long],Option[DateTime],Option[DateTime],Option[DateTime])) : Option[Order]= orderTuple match {
case (Some(id),Some(guestId),Some(created),delivered,paid) => Some(Order(Some(id),guestId,created,delivered,paid))
case (None,_,_,_,_) => None
}
So instead of using the values as they would be not-nulls, we provide a way to pass nulls, and then get an Option[Order] instead of an Order. We only have to match for Some(id) really, since if that's a null, all the other columns are null as well.
And so the original query looks like this:
for{
(guests,orders) <- guests(tableId) leftJoin Orders on (_.id === _.guestId)
} yield (guests,orders.maybe)
So finally this mystery is resolved now!
