Scala Slick 3.1 multiple joins

Question

I'm trying to perform multiple joins in Slick 3.1.1. The result that I would like to achieve is this:

SELECT * FROM customer LEFT JOIN customer_address ON customer.id = customer_address.customer_id LEFT JOIN address ON customer_address.address_id = address.id

I have tried the following:

val query = for {
  c <- Customer
  ca <- CustomerAddress if ca.customerId === c.id
  a <- Address if a.id === ca.addressId
} yield (c, a)

The problem here is that if a customer doesn't have an address that it doesn't appear which makes sense.

Then I tried this:

    val query2 = for {
       (c, ca, a) <- (Customer joinLeft CustomerAddress on (_.id === _.customerId)) joinLeft Address on (_._2.addressId === _.id)
    } yield (c, a)

The problem here is that I get an error on _._2.addressId because _._2 is a Rep object.

Exact error:

Error:(21, 110) value addressId is not a member of slick.lifted.Rep[Option[Models.Tables.CustomerAddress]]
  (c, ca, a) <- (Customer joinLeft CustomerAddress on (_.id === _.customerId)) joinLeft Address on (_._2.addressId === _.id)

Auto generated Slick Tables class: http://pastebin.com/e4M3cGU8

How can I get the results that I want with Slick?

Answer 1

What you have to do is also map the results.

val query2 = for {
      ((c, ca), a) <- (Customer joinLeft CustomerAddress on (_.id === _.customerId)) joinLeft Address on (_._2.map(_.addressId) === _.id)
} yield (c, a)

Answer 2

This should work. // ((c, ca), a) instead of (c, ca, a)

val query2 = for {
   ((c, ca), a) <- (Customer joinLeft CustomerAddress on (_.id === _.customerId)) joinLeft Address on (_._2.addressId === _.id)
} yield (c, a)

Answer 3

This answer is also like other answers, But more clear and more readable.

val query2 = for {
    ((customer, customerAddress), address) <-
        (Customer joinLeft CustomerAddress on
            {
                case(customerTable,customerAddressTable) =>
                    (customerTable.id === customerAddressTable.customerId)
            }
        ) joinLeft Address on
            {
                case ((customerTable,customerAddressTable),addressTable) =>
                    (customerAddressTable.map(_.addressId) === addressTable.id)
            }
} yield (customer, address)