Thanks for sharing your line of thought. That makes sense.
On Mon, Sep 20, 2021 at 11:09 AM Thomas Matthijs <[email protected]> wrote:
> Thanks, for the detailed & quick response
>
> Guess the reason for using both is mostly that I didn't know i was :)
>
> For example you start off with something like
>
> ctx.select(*)
> .from(Tables.FOO)
> .leftOuterJoin(Tables.BAR).onKey(Tables.FOO.bar_id.eq(Tables.BAR.id
> )
>
> Then you learn the code generation knows about your foreign keys
>
> ctx.select(*)
> .from(Tables.FOO)
> .leftOuterJoin(Tables.BAR).onKey()
>
> Then as queries get more complex, you start having trouble with how
> are all the tables with absolute references are connected/related to
> each other and if the query is correct or not,
> and you notice the Foo.bar() methods
>
> ctx.select(*)
> .from(Tables.FOO)
> .leftOuterJoin(Tables.FOO.bar()).onKey()
>
> has to be correct compiler verified!
>
> Then you look at some sql logging from the database and can't read
> anything, so you include some .as() aliases and everything is pretty
> (both generated sql and explicit in code what it does)
>
> Foo foo = Tables.FOO.as("foo");
> Foo bar = foo.bar().as("bar");
> ctx.select(*)
> .from(foo)
> .leftOuterJoin(bar).onKey()
>
> (And As i understand now possibly completely wrong usage of jooq?)
> (You need the onKey for things to compile)
>
It's not completely wrong, it just worked by accident (and it should work
in the future). But the ON KEY clause is still redundant. Once this works,
that particular syntax will probably have to repeat the ON predicate 2x,
once because of the implicit join usage, and once because of ON KEY, at
least that's what this API usage is telling jOOQ to do.
> So more correct usage would be like
> Foo foo = Tables.FOO.as("foo");
> Foo bar = foo.bar().as("bar");
> ctx.select(*)
> .from(foo, bar)
> .helloworld()
>
Yes that's what would be a desirable usage. HQL / JPQL supports precisely
this syntax, and I don't see why we shouldn't. Though, as I said, there
aren't any integration tests for this yet, so I can't tell you what the
current behaviour is, exactly.
As an aside: The reason why this hasn't been tested yet is because the most
common use case for putting implicit joins in FROM is to create to-many
joins (which jOOQ doesn't support via implicit joins yet), not to-one
joins. But obviously, as I'm seeing now, there's no reason not to use this
approach for to-one joins as well.
or just not reference any joins and let jooq figure it out?
The point of implicit joins is that you don't write explicit joins. The
join tree is created "implicitly"
> I'll have to do some more playing around and reread some documentation
> Can't do for example rightJoins then ?
jOOQ's implicit joins produce inner joins for "mandatory" foreign keys
(with a NOT NULL constraint) and left joins for "optional" foreign keys
(nullable). I don't think you would ever really need a right join?
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAB4ELO6z5D7Y__RLsXc%2BOUWuw8fbezTk92CqEZP%2BJdnQ3TGjQA%40mail.gmail.com.
