Joining onKey() with extra conditions

14 views
Skip to first unread message

Jens Teglhus Møller

unread,
Mar 14, 2022, 8:59:53 AMMar 14
to jOOQ User Group
Hi

When doing left outer joins, it would be a nice addition if you could use onKey() and add extra conditions (since you cannot alway put them in the where clause and get the same result).

Is there a way to do that or is it reasonably easy to implement or perhaps a workaround.

My current workaround is to do a regular .on() and provide the key columns manually. 

I was thinking you could either have onKey() return and SelectOnConditionStep so you could do onKey().and(...), this will on be backwards compatible so perhaps a new method onKeyWith(). Or perhaps a way for a regular .on() to use the default keys as in .on(key()).and(...).

And thanks for a great product btw.

Best regards Jens

Lukas Eder

unread,
Mar 14, 2022, 9:39:06 AMMar 14
to jOOQ User Group
Hi Jens,

Why not just call onKey(...).and(...) as you suggested?

Thanks,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/6b843b80-afa4-4e3f-a0dd-070b9d3d0164n%40googlegroups.com.

Jens Teglhus Møller

unread,
Mar 14, 2022, 11:01:25 AMMar 14
to jOOQ User Group
Hi Lukas

The onKey() method returns a SelectJoinStep which does not have .and()/.or() etc.

I'm on jooq 3.14.15 (provided by spring boot 2.5.10), so maybe this has changed in later version, but I could not see it in https://www.jooq.org/javadoc/3.16.x/org.jooq/org/jooq/SelectJoinStep.html

Best regards Jens

Lukas Eder

unread,
Mar 14, 2022, 11:32:03 AMMar 14
to jOOQ User Group
It's right there, returning SelectOnConditionStep:

But you're right, that hasn't been available up until recently (jOOQ 3.16.0):

Lukas Eder

unread,
Mar 14, 2022, 11:33:45 AMMar 14
to jOOQ User Group
Note, if you can't upgrade, no problem. Just use TableOnStep.onKey() instead:

Knowing that the SelectJoinStep API is mere convenience, i.e. these are the same (check the parentheses):

ctx.select().from(T1).join(T2).on(condition);
ctx.select().from(T1.join(T2).on(condition));

The latter syntax always supported combining onKey().and()

Jens Teglhus Møller

unread,
Mar 15, 2022, 5:21:13 AMMar 15
to jOOQ User Group
Thanks a lot, I will override the spring provided version and move to 3.16.5.

Best regards Jens

Reply all
Reply to author
Forward
0 new messages