CODEWITHSUNDEEP
scalaslickslick-3.0
ps0604
ps0604

Reputation: 1071

Left outer join of three tables with two conditions each not working in Slick

I have the following left outer join that I'd like to represent in Slick:

select * from tableD d 
left outer join tableE e on e.ds_sk=d.ds_sk and e.ds_type = d.ds_type
left outer join tableV v on v.es_sk=e.sk and v.var_name=d.var_name
where e.sk=30;

Note that each relationship contains two join conditions. The first join conditions are between tableD and tableE, and the second join conditions are between tableV, tableE and tableD. Finally, there's also a where condition.

This is my attempt (that throws a compilation error):

  val query = for {
    ((d, e), v) <- tableE joinLeft tableD on ((x,y) => x.dsType === y.dsType && x.dsSk === y.dsSk) 
                          joinLeft tableV on ((x,y) => x._1.sk === y.esSk && x._2.varName === y.varName) 
                   if (e.sk === 30) 
  } yield (d,e,v)

The error I get is:

◾value varName is not a member of slick.lifted.Rep[Option[tableD]]

What is the error and how to fix this code?

Upvotes: 0

Views: 423

Answers (2)

Rodo
Rodo

Reputation: 332

You can compare doing map by varName, example:

...  && x._2.map(_.varName) === y.varName ...

Here I leave a similar example:

  val withAddressesQuery = for {
   (((person, phone), _), address) <- withPhonesQuery.
           joinLeft(PersonAddress).on(_._1.personId === _.personId).
           joinLeft(Address).on(_._2.map(_.addressId) === _.addressId)
  } yield (person, phone, address)

Notice: ... on(_._2.map( _.addressId) === ...see complete

There's some more information about joinLeft in Chapter 6.4.3 of Essential Slick.

Upvotes: 0

Leo C
Leo C

Reputation: 22439

There seems to be discrepancies between your SQL query (D leftjoin E leftjoin V) and Slick query (E leftjoin D leftjoin V).

Assuming your SQL query is the correct version, the Slick query should look like the following:

val query = for {
  ((d, e), v) <- tableD joinLeft tableE on ( (x, y) =>
                   x.dsType === y.dsType && x.dsSk === y.dsSk ) 
                        joinLeft tableV on ( (x, y) =>
                   x._2.map(_.sk) === y.esSk && x._1.varName) === y.varName ) 
                   if (e.sk === 30) 
  } yield (d,e,v)

Note that in the second joinLeft, you need to use map to access fields in tableE which is wrapped in Option after the first joinLeft. That also explains why you're getting the error message about Option[tableD] in your Slick query.

Upvotes: 1

Related Questions