Scala slick left join doesnt work

Question

Im a newbie to Slick.

I would like to get the list of ids from Echo table that DO NOT exist in programs_vw table.

I have written the SQL query which works

SELECT f.`id`
FROM `Full`.`programs_vw` f
LEFT JOIN `FULL`.`Echo` e ON f.`id`=e.`id`
WHERE e.`id` IS NULL
ORDER BY f.`id`;

I have referred to the examples in http://slick.lightbend.com/doc/3.0.0/queries.html and wrote this but it doesnt work

 val query = for {
      (t, f) <- echoQuery.filter(_.id.isEmpty) join programsVwQuery on(_.id === _.id)
      } yield (f.id)
db.run(query.to[List].result)

Could you explain a little bit more why it doesn't work? What's the output? — Yuchen

Paul Dolega Paul Dolega · Accepted Answer · 2016-12-08T07:24:25

First of all what you did is not a LEFT JOIN (I am talking about Slick version). In order to generate it you need to use joinLeft method.

However this straightforward correction is wrong - it produces subquery which is bad.

for {
      (_, p) <- echoQuery.filter(_.id.isEmpty)
               .joinLeft(programsVwQuery).on(_.id === _.id)
} yield (p.map(_.id))

Side note: Keep in mind that p above is an Option (it's LEFT JOIN after all).

Corrected solution would be something like this:

for {
      (e, p) <- echoQuery
               .joinLeft(programsVwQuery).on(_.id === _.id) if e.id.isEmpty
} yield (p.map(_.id))

Which in my opinion is a good sign - it actually reads almost like SQL.

Fully correct solution

Above generates a kind of join you want without subqueries but if you compare it to your desired query, it actually doesn't produce what you are aiming at. If Slick can often be read as SQL than if our SQL is like this (your desired version):

SELECT f.`id`
FROM `Full`.`programs_vw` f
LEFT JOIN `FULL`.`Echo` e ON f.`id`=e.`id`
WHERE e.`id` IS NULL
ORDER BY f.`id`;

than exact mapping to Slick version would look like this:

 val query =
 (for {
          (p, e) <- programsVwQuery
                    .joinLeft(echoQuery).on(_.id === _.id) if e.map(_.id).isEmpty
 } yield (p.id))).sortBy(id => id)

db.run(query.result)  // this runs the query

Basically you go exactly as in SQL in this case. It matches your desired query perfectly. If you take a look at generated SQL is exactly what you wanted at the beginning.

Scala slick left join doesnt work

1 Answers