left outer join WITH inner join

[Will Chan]

Hi, Maxime and others. First of all, thanks again for all your work on Squeryl, Maxime.

I would like to do both a left outer join and multiple inner joins, but I can't figure it out.

I basically want to write code that does this:

val myDepartmentId = 1

join(students, addresses.leftOuter, emails.leftOuter, advisors, labs)(

  (student, address, email, advisor, lab) =>

    select(student, address, email)

    on(student.id === address.map(_.studentId), student.id === email.map(_.studentId), student.advisorId === advisor.id, advisor.labId === lab.id, lab.department === myDepartmentId)

)

Let's say that student has an advisorId, advisor has a labId, and lab has a departmentId. I want the address and email joins to be a left outer joins and the restriction on advisor/lab/department to be inner joins.

With the current code, I ran into the problem of "If a join has N arguments, the ‘on’ function must take N-1 arguments".

So I rewrote the on clause to be "on(student.id === address.map(_.studentId), student.id === email.map(_.studentId), student.advisorId === advisor.id, advisor.labId in labIds)", but I got twice as many results as I was supposed to get.

I would appreciate any advice you could give.

Best Regards,

Will Chan

[Roland Kaercher]

Hi Will,

am I right that what is causing the unwanted additional results is the
missing department id restriction? If so, the following could work for
you:

val myDepartmentId = 1
join(students, addresses.leftOuter, emails.leftOuter, advisors, labs)(
(student, address, email, advisor, lab) =>

where(lab.department === myDepartmentId)

select(student, address, email)
on(student.id === address.map(_.studentId), student.id ===
email.map(_.studentId), student.advisorId === advisor.id,

advisor.labId === lab.id)
)

Kind regards,

Roland

[David Whittaker]

Yup, I think Roland's correct.  In your original on(...) clause the last statement does not join tables.  It should be included as a where(...).

[Will Chan]

Roland and David,

Thanks so much! Adding the restriction in the where clause worked like a charm.