multiple joins with slick

Question

For joining between two tables is done like

    (for {
    (computer, company) <- Computers leftJoin Companies on (_.companyId === _.id)
    if computer.name.toLowerCase like filter.toLowerCase()
    }

But in case if joining required between more tables what is the right way trying below but doesnt work

   (for {
    (computer, company,suppliers) <- Computers leftJoin Companies on (_.companyId ===        _.id)
     //not right leftjoin Suppliers on (_.suppId === _.id)
    if computer.name.toLowerCase like filter.toLowerCase()
  }

Solution 1

The first join results in a Query returning Tuples. One of the tuple components has the foreign key you want to use for the second join. You need to get this component in the second join condition before getting its field. If Companies is the table, that has the field suppId it would look like this:

(for {
  ((computer, company),suppliers) <- Computers leftJoin Companies on (_.companyId === _.id) leftJoin Suppliers on (_._2.suppId === _.id)
  if computer.name.toLowerCase like filter.toLowerCase()
} yield ... )

Solution 2

I think you want this:

for {
    (computer, company) <- Computers leftJoin Companies on (_.companyId === _.id)
    (supp, _) <- company innerJoin Suppliers on (_.suppId === _.id)
    if computer.name.toLowerCase like filter.toLowerCase()
} yield (computer, company, supp)

Of course, I am making assumptions about your model. Not sure if suppliers are linked with companies or computers.

Comments

[dsr301]

computer is associated with supplier

[dsr301]

here leftJoin Suppliers on (._2.suppId === _.id) is this join on the first tuple? in case if want to join between computer and suppliers i am trying with leftJoin Suppliers on (._1.suppId === _.id) but this one fails

[dsr301]

This works, but i see that the full query with joins should be in one line , else its showing compilation errors

[cvogt]

changed the answer to be in one line

[Eric Pabst]

I like that the second join is a separate line in the "for" comprehension.

[Setheron]

how does company do the inner join here ? It a model type? (second line in for loop)

[pedrofurla]

company is the one defined in the previous line.

[AlixB]

Because you can't reuse company as a Query, it is a Projection. I tested this solution 3 times on Slick 2.1 :p

[pedrofurla]

Look at the date of question and date of the answer. Anyways, if I get time I will amend the question.