Scala Slick 3 - How to get non-matching results on joinLeft?

Question

I would like to join two tables and get the rows from the first table that don't have a matching row in the second table for some condition of a certain column

for example:

tableA.joinLeft(tableB)
   .on((a: A, b: B) => a.key === b.key && a.field1 =!= b.field1)
   .filter(_._2.map(_.key).isEmpty)
   .map(_._1)

but this checks that key==null in tableB instead of checking on the result of the join. What am I doing wrong?

Answer 1

I've found a solution by splitting it into 2 queries: one query is:

tableA.join(tableB)
.on((a: A, b: B) => a.key === b.key)
.filter((a: A, b: B) => a.field1 =!= b.field1)
.map(_._1)

second query is:

tableA.filterNot(_.key in tableB.map(_.key))

And then "union" the two queries

Answer 2

Perhaps you need a full outer join, and then filter on result rows where the second table entry is None (NULL). For example:

tableA.fullJoin(tableB)
   .on((a: A, b: B) => /* your join condition here */)
   .filter { case (_, maybeMissing) => maybeMissing.isEmpty }
   .map { case (first, _) => first }