Slick 3 multiple outer joins

Question

from Slick documentation, it's clear how to make a single left join between two tables.

val q = for {
  (t, v) <- titles joinLeft volumes on (_.uid === _.titleUid)
} yield (t, v)

Query q will, as expected, have attributes: _1 of type Titles and _2 of type Rep[Option[Volumes]] to cover for non-existing volumes.

Further cascading is problematic:

val q = for {
  ((t, v), c) <- titles 
                     joinLeft volumes on (_.uid === _.titleUid)
                     joinLeft chapters on (_._2.uid === _.volumeUid)
} yield /* etc. */

This won't work because _._2.uid === _.volumeUid is invalid given _.uid being not existing.

According to various sources on the net, this shouldn't be an issue, but then again, sources tend to target different slick versions and 3.0 is still rather new. Does anyone have some clue on the issue? To clarify, idea is to use two left joins to extract data from 3 cascading 1:n:n tables. Equivalent SQL would be:

Select *
from titles
left join volumes
    on titles.uid = volumes.title_uid
left join chapters
    on volumes.uid = chapters.volume_uid

Sean Vieira Sean Vieira · Accepted Answer · 2015-08-18T02:38:35

Your second left join is no longer operating on a TableQuery[Titles], but instead on what is effectively a Query[(Titles, Option[Volumes])] (ignoring the result and collection type parameters). When you join the resulting query on your TableQuery[Chapters] you can access the second entry in the tuple using the _2 field (since it's an Option you'll need to map to access the uid field):

val q = for {
  ((t, v), c) <- titles 
                     joinLeft volumes on (_.uid === _.titleUid)
                     joinLeft chapters on (_._2.map(_.uid) === _.volumeUid)
} yield /* etc. */

Avoiding `TupleN`

If the _N field syntax is unclear, you can also use Slick's capacity for user-defined record types to map your rows alternatively:

// The `Table` variant of the joined row representation
case class TitlesAndVolumesRow(title: Titles, volumes: Volumes)

// The DTO variant of the joined row representation
case class TitleAndVolumeRow(title: Title, volumes: Volume)

implicit object TitleAndVolumeShape
  extends CaseClassShape(TitlesAndVolumesRow.tupled, TitleAndVolumeRow.tupled)

Slick 3 multiple outer joins

1 Answers

Avoiding TupleN

Avoiding `TupleN`