Complex Slick Join Query

Question

I have multiple tables and I need to return them all together with a Slick query.

case class A(id: Int, name: String)
case class B(id: Int, name: String)
case class C(id: Int, name: String)
case class AtoC(aId: Int, dId: Int)
case class D(id: Int, name: String)

Assuming I have Table definitions matching the above case classes, I want to return something like (A, B, C, Seq[D]) but I cannot find a way to write it where it will even compile.

I have tried something like this:

for {
    a <- AQuery.innerJoin(B)....
    ...
    AtoC <~ a.innerJoin(AtoCQuery).on(....)

but this won't even compile.

score 1 · Accepted Answer · edited Dec 11 '14 at 06:29

1

Try something like this:

val q = for (
  ((((A, B), AtoC), C), D)
  <- AQuery.innerJoin(BQuery).on(_.id === _.id)   // A.id == B.id
    .innerJoin(AtoCQuery).on(_._1.id === _.aId)   // A.id == AtoC.aId
    .innerJoin(CQuery).on(_._2.cId === _.id)      // AtoC.cId == C.aId
    .innerJoin(DQuery).on(_._1._1._1.id === _.id) // A.id == D.id
) yield (A, B, C, D)
val result = q.run

This code is not tested. Hope it helps !

Regards

edited Dec 11 '14 at 06:29

Benny

3,899
8
46
81

answered Dec 10 '14 at 12:19

Daniel Riquelme

301
4
9

Thanks! This appears to work, but how do I end up with the ultimately desired tuple: `(A, B, C, Seq[D])` the `Seq[D]` part being the difference? – Benny Dec 11 '14 at 05:12
Try replacing the D in yield (A, B, C, D) with another for comprehension, something like `(A, B, C, ( for( ...) yield D ).list)` – Daniel Riquelme Dec 11 '14 at 13:04
`D` in this context isn't iterable though, so what would the content of my for comprehension be? – Benny Dec 11 '14 at 19:45
You're right, try putting the inner for comprehension between parenthesis and call `.list` on it – Daniel Riquelme Dec 12 '14 at 02:03

Complex Slick Join Query

1 Answers1