Scala Slick filter with conditions over two left joined tables

Question

I am trying to reproduce this query in Slick.

SELECT *
FROM A
JOIN LEFT B AS B1 ON B1.aId = A.id && B1.condition = 'b1'
JOIN LEFT B AS B2 ON B2.aId = A.id && B2.condition = 'b2'

- (no condition, the query in a plain way)

- WHERE B1.status = 'delete' OR B2.status = 'delete'

- WHERE ((B1.status = 'read' AND B2.status <> 'delete') OR (B1.status <> 'delete' AND B2.status = 'read')

- WHERE B1.status = 'write' AND B2.status = 'write'

- WHERE B1.status = 'full' AND B2.status = 'full'

- WHERE ((B1.status = 'full' AND B2.status = 'write') OR (B1.status = 'write' AND B2.status = 'full')

I am not sure if this is possible

Up to now I have something like this

val query = for { ((a, b1Opt), b2Opt) <- ATable.aQuery join
  BTable.BQuery on ((joinTable, bTable) => join._1.id === _.AId && bTable.condition === "b1") join
  BTable.BQuery on ((joinTable, bTable) => join._1.id === _.AId && bTable.condition === "b2")
} yield (a, b1Opt, b2Opt)

and I am trying something like this

val filterB = query {
  case (a, b1Opt, b2Opt) => {
    bStatus match {
      case "delete" => b1Opt.map(b1 => b1.status === "delete") || b1Opt.map(b2 => b2.status === "delete")
    }

  }
}

Answer 1

From what you've described, the two successive left joins of table B on table A's id should translate to something similar to the following:

val joinQuery = for {
  ((a, b1), b2) <- tableA joinLeft tableB on ( (x, y) =>
                            x.id === y.aId && y.condition === "b1" )
                          joinLeft tableB on ( (x, y) =>
                            x._1.id = y.aId && y.condition === "b2" )
} yield (a, b1, b2)

And a where condition of B1.status = 'delete' and B2.status = 'delete' should look like this:

val filterB = joinQuery.filter{ case (_, b1, b2) =>
  b1.filter(_.status === "delete").isDefined && b2.filter(_.status === "delete").isDefined
}

Note that with the left joins, b1 and b2 are wrapped in Option, hence the using of isDefined for the and operation.

As another side note, it might be worth considering to filter table B with B.condition = 'b?' to a reduced B1 and B2 before performing the left joins.