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

Trellas

possible duplicate of multiple joins with slick – Slusher

that answer is for Slick 2 – Axletree

Answer 1

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)

Slusher

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. – Trellas

@Bruno - updated with what I believe is the correct syntax for 3.0 – Slusher

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! – Trellas

It compiles if you put all those joinLefts in one line. At least for me. – Symposiarch