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?

Adala

Can you please add the error you receive? – Tabathatabb

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)

Exciseman

this is the right answer but this syntax is nauseating. i thought slick would solve problems for me but i've just been met with frustrations like this – Ajani

Is there any cleaner way introduced in slick to do this yet? This is quite common use-case (at least for us) and this makes code really ugly! – Foreplay

Answer 2

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)

Ahouh

Answer 3

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)

Heterogenesis

@JellevanEs Looks like the configuration for Table is wrong. Would it be possible to share Customer, CustomerAddress and Address definition? Look like you have missed using Option in one of those table definition? – Heterogenesis

I added the Table class. I don't know if you can do something with it because it is auto generated by slick – Adala