Multiple left joins

[Julien Richard-Foy]

Hi, I tried to translate this piece of code: https://gist.github.com/2788715 using ScalaQuery. The result is here: https://gist.github.com/c1d6f4faf7f06e04e4d3

I’d like to know if I’m using ScalaQuery in the right way or if I did some mistakes, if it’s possible to make the code more elegant, etc.

I was unable to rewrite the following query: https://gist.github.com/2788715#L271

It uses two left joins. So I rewrote it in three queries: https://gist.github.com/c1d6f4faf7f06e04e4d3#L274

Was there a better way to handle multiple left joins?

By the way, I looked at the SQL code generated by a query composing subqueries: https://gist.github.com/c1d6f4faf7f06e04e4d3#L191

(For info, the original query was: https://gist.github.com/2788715#L217)

This query uses these two subqueries: https://gist.github.com/c1d6f4faf7f06e04e4d3#L43 and https://gist.github.com/c1d6f4faf7f06e04e4d3#L45

The generated code is the following:

SELECT "t1"."ID","t1"."TITLE","t1"."SUMMARY","t1"."START","t1"."END","t1"."PLACE","t1"."IMAGEURL","t1"."AUTHOR","t2"."EMAIL","t2"."NAME","t4"."ID","t4"."TITLE","t4"."SUMMARY","t4"."IMAGEURL","t4"."AUTHOR"
FROM "EVENTS" "t5","EVENTS_SUBJECTS" "t6" inner join "SUBJECTS" "t4"
on ("t6"."SUBJECT_ID"="t4"."ID"),"EVENTS" "t1","USERS" "t2"
WHERE ("t1"."ID"=42) AND ("t2"."EMAIL"="t5"."AUTHOR") AND
("t6"."EVENT_ID"="t5"."ID")

What’s weird to me is that the EVENTS table is used two times (it’s aliased in t1 and t5). Will the result be correct?

Regards,

Julien

[virtualeyes]

Julien,

I don't believe ScalaQuery supports joins beyond 3 levels deep, and
even then, as you can see, it's fairly cumbersome syntax-wise.

Something like this should work:
(for {
l <- logs
u <- users if l.user_id is u.email
e <- events if e.events.id is l.event_id
s <- subjects if s.id is l.subject_id
_ <- Query orderBy l.at.desc
} yield ((l.at,l.kind),u,e,s)) take(20)

btw, please get 22 tuple support added to Forms.scala mapping in Play
2.0 master!
https://github.com/playframework/Play20/blob/master/framework/src/play/src/main/scala/play/api/data/Forms.scala#L125

Right now you guys support up to 18 tuple, which is killing me in some
ScalaQuery models that are at the 22 tuple max supported by Scala
lanaguage itself.

Thanks

On May 27, 2:06 pm, Julien Richard-Foy <julien...@gmail.com> wrote:
> Hi, I tried to translate this piece of code:https://gist.github.com/2788715using
> ScalaQuery. The result is here:https://gist.github.com/c1d6f4faf7f06e04e4d3
> I’d like to know if I’m using ScalaQuery in the right way or if I did some
> mistakes, if it’s possible to make the code more elegant, etc.
>
> I was unable to rewrite the following query:https://gist.github.com/2788715#L271
> It uses two left joins. So I rewrote it in three queries:https://gist.github.com/c1d6f4faf7f06e04e4d3#L274
> Was there a better way to handle multiple left joins?
>
> By the way, I looked at the SQL code generated by a query composing
> subqueries:https://gist.github.com/c1d6f4faf7f06e04e4d3#L191
> (For info, the original query was:https://gist.github.com/2788715#L217)

> This query uses these two subqueries:https://gist.github.com/c1d6f4faf7f06e04e4d3#L43andhttps://gist.github.com/c1d6f4faf7f06e04e4d3#L45

[Dan Carleton]

Does Slick 1.0.0 still not support more than one left join in a query?  I have a table with two Option foreign keys to the same second table, and I'm having trouble left joining the three.

Thanks,

- Dan

[ijuma]

On Sunday, 6 January 2013 18:17:12 UTC+2, Dan Carleton wrote:

Does Slick 1.0.0 still not support more than one left join in a query?  I have a table with two Option foreign keys to the same second table, and I'm having trouble left joining the three.

It does support more than one left join, but the syntax is clunky:

https://github.com/slick/slick/blob/master/slick-testkit/src/main/scala/com/typesafe/slick/testkit/tests/NewQuerySemanticsTest.scala#L391

Best,

Ismael

[Dan Carleton]

This works!  Thank you.

- Dan

[Pradeep Kumar Mishra]

I couldn't understand this test case. Could you please explain how can I use multiple left join returning data from the all the tables participating in left join?