Joining onKey() with extra conditions

[Jens Teglhus Møller]

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]

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]

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]

It's right there, returning SelectOnConditionStep:

https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/SelectOnStep.html#onKey()

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

https://github.com/jOOQ/jOOQ/issues/12200

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/501e5ac0-336e-48d8-8831-22fd0a193d6cn%40googlegroups.com.

[Lukas Eder]

Note, if you can't upgrade, no problem. Just use TableOnStep.onKey() instead:

https://www.jooq.org/javadoc/3.14.15/org.jooq/org/jooq/TableOnStep.html#onKey()

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]

Thanks a lot, I will override the spring provided version and move to 3.16.5.

Best regards Jens