Using arbitrary SQL string as a CTE in conjunction with the DSL

[Max Kremer]

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

[Lukas Eder]

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

  

--
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.
For more options, visit https://groups.google.com/d/optout.

[Max Kremer]

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?

[Lukas Eder]

Hi Max,

Yes, that's possible of course. Don't know why I hadn't thought of that.

Slightly better than resorting to using toString() is using jOOQ's plain SQL templating mechanism. Write:

dslContext.fetch(

    "WITH myCte AS (select * from foo) {0}",

    select().from("myCte").where(...).groupBy(...)

);

This way, you can still profit from bind variables, sql injection safety, etc.

Hope this helps,

Lukas

[Max Kremer]

Nice! Great suggestion, thanks again Lukas