Slick left outer join fetching whole joined row as option

Question

My join looks like this:

def byIdWithImage = for {
    userId <- Parameters[Long]
    (user, image) <- Users leftJoin RemoteImages on (_.imageId === _.id) if user.id === userId
} yield (user, image)

but slick fails at runtime when user.imageId is null

[SlickException: Read NULL value for column RemoteImage.url]

Changing the yield to

} yield (user, image.?)

gives me a compile time exception, it only works on individual columns

could not find implicit value for evidence parameter of type scala.slick.lifted.TypeMapper[image.type]

Would there be a different way to accomplish what I'm trying to do here? (in a single query)

Somewhat half fixed in slick 2.0 => http://slick.typesafe.com/talks/2013-12-03_Scala-eXchange/2013-12-03_Patterns-for-Slick-database-applications-Scala-eXchange.pdf — Somatik, Dec 05 '13 at 09:37

score 8 · Answer 1 · answered Feb 21 '13 at 08:56

Off the top of my head, I'd use a custom mapped projection. Something like this:

case class RemoteImage(id: Long, url: URL)

def byIdWithImage = for {
    userId <- Parameters[Long]
    (user, image) <- Users leftJoin RemoteImages on (_.imageId === _.id) if user.id === userId
} yield (user, maybeRemoteImage(image.id.? ~ image.url.?))

def maybeRemoteImage(p: Projection2[Option[Long], Option[URL]]) = p <> ( 
  for { id <- _: Option[Long]; url <- _: Option[URL] } yield RemoteImage(id, url),
  (_: Option[RemoteImage]) map (i => (Some(i id), Some(i url)))
)

Using scalaz (and its ApplicativeBuilder) should help reduce some of that boilerplate.

thanks, would be nice if slick would provide a helper for this case — Somatik, Feb 21 '13 at 10:49

score 8 · Accepted Answer · edited Dec 05 '13 at 12:28

With the code below you can put it like: yield (user, image.maybe)

case class RemoteImage(id: Long, url: URL)

class RemoteImages extends Table[RemoteImage]("RemoteImage") {
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def url = column[URL]("url", O.NotNull)
    def * = id.? ~ url <> (RemoteImage.apply _, RemoteImage.unapply _)

    def maybe = id.? ~ url.? <> (applyMaybe,unapplyBlank)

    val unapplyBlank = (c:Option[RemoteImage])=>None        

    val applyMaybe = (t: (Option[Long],Option[URL])) => t match {
        case (Some(id),Some(url)) => Some(RemoteImage(Some(id),url))
        case _ => None
    } 
}

score 1 · Answer 3 · edited Oct 31 '13 at 09:18

1

I integrated helpers for this in my play-slick example app, which allow you to just call image.?

See the .? calls, the definition of ? and the definition of mapOption.

edited Oct 31 '13 at 09:18

Community

1
1

answered Sep 28 '13 at 04:39

cvogt

11,260
30
46

will this be part of slick 2.0? – Somatik Dec 05 '13 at 13:22
Not this implementation, but probably our code generator will generate the required ? methods. So in a way, probably yes. – cvogt Dec 05 '13 at 17:04

Slick left outer join fetching whole joined row as option

3 Answers3

Linked