14

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
Bruno Batarelo
  • 315
  • 2
  • 11

1 Answers1

28

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)
Sean Vieira
  • 155,703
  • 32
  • 311
  • 293
  • Hi Sean - I corrected my code in question and added _2.uid. That is actually the problem given that in expression: joinLeft chapters on (_._2.uid === _.volumeUid) _._2 is not of a type Volumes, but it is of a type Rep[Option[Volumes]]. Therefore _._2.uid is not available and that is the source of issue. – Bruno Batarelo Aug 18 '15 at 11:07
  • @Bruno - updated with what I *believe* is the correct syntax for 3.0 – Sean Vieira Aug 18 '15 at 13:06
  • I marked the answer as correct given that it does compile. However, I couldn't test it completely unfortunately because of other issues in slick I'm currently facing. Thanks! – Bruno Batarelo Aug 19 '15 at 11:55
  • It compiles if you put all those `joinLeft`s in one line. At least for me. – omnomnom Apr 24 '17 at 12:55