Thank you Lukas. What about this idea:
I can use the current JOOQ based query code and instead of calling fetch() I can call toString(). So what I would do is simply pretend my CTE ( or view ) exists in when writing the JOOQ query. Then get that query as a string and prepend to that the CTE (by writing plain SQL by hand) Something like this: String sql = dslContext.select() .from( "myCte" ) .where( ... ) .groupBy( ... ) .toString(); String cte = "WITH myCte AS ( select * from foo) "; dslContext.execute(cte + sql); What do you think? On Friday, November 6, 2015 at 3:51:58 AM UTC-5, Lukas Eder wrote: > > Hello Max, > > Unfortunately, this is currently not possible as the CTE syntax requires > an org.jooq.Select type, instead of an org.jooq.ResultQuery type. There's a > pending feature request to improve this: > https://github.com/jOOQ/jOOQ/issues/4474 > > In particular, some databases like PostgreSQL also allow DML statements as > CTE bodies: > > WITH cte AS ( > INSERT INTO table (a, b) > VALUES (1, 2) > RETURNING * > ) > SELECT * FROM cte > > > So, there's a whole set of API changes that we'd like to implement to > enable these features. > > One workaround I can think of right now is for you to implement a > reflection proxy that implements org.jooq.Select and delegates all calls to > a backing plain SQL ResultQuery. It doesn't sound like a very robust > workaround, though. > > Another option is not to use a CTE, but a derived table, where plain SQL > is perfectly possible. > > Finally, if you externalise the CTE into a view, you can re-use the SQL > string even more easily. > > I hope this helps, > Lukas > > > 2015-11-06 1:15 GMT+01:00 Max Kremer <[email protected] <javascript:>>: > >> Hello fellow JOOQers >> >> I have a need to mix raw SQL strings with SQL generated by the DSL. >> >> I have a CTE which is far too cumbersome to express in jooq - for the >> sake of simplicity however lets say my cte is >> SELECT foo FROM bar >> >> I would like to do something like this >> >> String myCte = "SELECT foo FROM bar"; >> >> DSL.with("mycte").as(myCte) >> .select( ... ) >> .from(... ) >> .where( ... ) >> .fetch().steam(). >> //etc.. >> >> >> >> So basically what I want to do is mix SQL strings and the DSL. Doesn't >> seem like this is possible because with("mycte").as() takes Select<?> as a >> param and not a String. >> >> Is what I want to do possible or just too crazy? >> >> Thanks, >> >> Max >> >> -- >> 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] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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/d/optout.
