14

Doing an outer join between two tables is easy:

tblA.leftJoin(tblB).on(_.aId === _.bId)

But when doing joins between 5 tables it quickly becomes unwieldy:

tblA.leftJoin(tblB).on(_.aId === _.bId).
    leftJoin(tblC).on(_._1.aId === _.cId).
    leftJoin(tblD).on(_._1._1.aId === _.dId).
    leftJoin(tblE).on(_._1._1._1.aId === _.eId)

The tuple accessors become a little confusing, especially if they do not all access the first table.

Is there a better way?

The fact that inner joins are semantically equivalent to flatMap with filter solves this problem for inner joins:

for {
    a <- tblA
    b <- tblB if a.aId === b.bId
    c <- tblC if a.aId === c.cId
    d <- tblD if a.aId === d.dId
    e <- tblE if a.aId === e.eId
} yield ???

Is there a similairly elegant syntax for outer joins?

Martin Kolinek
  • 2,010
  • 14
  • 16

1 Answers1

6

How about this?

tblA.leftJoin(tblB).leftJoin(tblC).leftJoin(tblD).leftJoin(tblE).on{
  case ((((a,b),c),d),e) =>
    a.aId === b.bId &&
    a.aId === c.cId &&
    a.aId === d.dId &&
    a.aId === e.eId
}

Doesn't produce the best SQL at the moment, but we hope to improve that soon.

A left join producing HLists should allow to even make the pattern match nicer, but is probably blocked by: https://github.com/slick/slick/issues/728

cvogt
  • 11,260
  • 30
  • 46
  • 1
    I tried this approach and the SQL query that slick generates contains some syntax error and MySQL can't run it. There is also to say that the query that is generated by doing multiple left joins in other ways is really slow to execute on MySQL. Is there a solution for these issues? – Aldo Stracquadanio May 08 '14 at 08:58
  • If it produces invalid SQL make sure you use Slick 2.0.1 and if it still does, please report a bug with reproduction steps and the invalid SQL it produced. We are aware of the issue, that the MySQL optimizer seems to be really bad with sub-selects, which Slick currently generates unnecessarily in some cases. Did you try MySQL 5.6? The optimizer was improved significantly. Also Slick will probably improve in that area at some point. Here is a pattern to swap out non-optimal SQL with optimized SQL by hand: https://gist.github.com/cvogt/8054159 it needs to be adapted to Slick 2 though. – cvogt May 08 '14 at 12:29
  • I'm using Slick 2.0.1 and in our company we are MySQL 5.5, so I didn't try it on 5.6. I will try to reproduce the failing query outside of our code and I will open an issue if I succeed in that. Thank you for the pointers anyway! – Aldo Stracquadanio May 08 '14 at 15:18
  • Using Slick 2.1.0 I also got invalid SQL (a referenced column did not exist). ("Doesn't produce the best SQL": derived tables are involved, which is something you probably wouldn't do if writing A LEFT JOIN B LEFT JOIN C by hand.) – qu1j0t3 May 22 '15 at 02:03
  • Try 3.0. Slick 3.1 will improve the structure of the generated SQL – cvogt May 22 '15 at 17:19