Hello, 2013/9/10 Nicholas Ustinov <[email protected]>
> Hello! > I tried to search info on supporting by JOOQ of common table expressions > and recursive ones (for hierarchical queries), but didn't find too much > info. > I found > http://blog.jooq.org/2011/10/02/recursive-queries-with-oracles-connect-by-clause/with > some promises about applying oracle syntax ("In the near future, jOOQ > is going to project the CONNECT BY syntax and API to other RDBMS’s Common > Table Expression syntax. That way, you can express hierarchical queries in > any database supporting CTE’s using Oracle’s CONNECT BY syntax.") > Yes, that was an optimistic perception of "near future". I have given this some thought. Emulation of CONNECT BY through CTE is a non-trivial task, if all pseudo-columns should be supported as well. And then, unfortunately, there had also been other priorities. So at all: does JOOQ support CTE (it may be useful in oracle too for other > reasons than hierarchical queries), recursive CTE or another syntax for > hierarchical queries in other databases than Oracle? (for now I need > PostgreSQL and MSSQL Server) > I agree that CTE are a very useful feature. They have been on the roadmap for a while: https://github.com/jOOQ/jOOQ/issues/454<https://github.com/jOOQ/jOOQ/issues/454?source=cc> So, let's reason about syntax translation: 1. The WITH [ RECURSIVE ] clause can easily be preprended to SELECT statements (in PostgreSQL also to other statements) 2. This clause would accept an AliasedTable<?>... argument To make CTE somewhat useful within jOOQ, a new marker type should probably be introduced, marking aliased tables: AliasedTable<R> a = MY_TABLE.as("a"); AliasedField<T> f = MY_TABLE.MY_FIELD.as("f"); While maintaining all existing Table<?> functionality, these marker types can then be used in jOOQ's DSL to ensure that only previously aliased tables (e.g. derived tables) can be passed to the with method. Users are free to chose whether they want to alias only table names, or also column names through derived column lists. Example from the PostgreSQL documentation http://www.postgresql.org/docs/9.3/static/queries-with.html: WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t ) SELECT n FROM t LIMIT 100; This would then translate to this usage in jOOQ: AliasedTable<Record1<Integer>> t = table( select(val(1)) .unionAll( select(field("n").add(1)) // Cannot refer to n here... .from("t")) // Cannot refer to t here... ).as("t", "n") DSL.withRecursive(t [, ...]) .select(t.field("n")) .from(t) .limit(1000); This is just one example. As you can see, there isn't a lot of typesafety in the above, especially when recursive CTE are involved. I'm very open to discussing alternative / complementary APIs. For instance (complete lack of typesafety): DSL.withRecursive("t", "n").as( select(val(1)) .unionAll( select(field("n").add(1)) .from("t")) ) [.with(...) ...] .select(field("n")) .from("t") .limit(1000); Feedback welcome! Cheers 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
