Guess, the problem is that in my metal modal i was never using implicit joins and saw it more like the Foo.bar() is a reference to bar table that knows to use that primary key in joins
On Mon, 20 Sept 2021 at 11:19, Lukas Eder <[email protected]> wrote: > > 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. -- 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/CABY_-Z7NQWTP%2BvHyVtarx5xLBxsruQ7NKS56Q8cY418oUJ6Oaw%40mail.gmail.com.
