Nice! Great suggestion, thanks again Lukas
On Friday, November 6, 2015 at 11:59:02 AM UTC-5, Lukas Eder wrote:
>
> 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
>
> 2015-11-06 16:54 GMT+01:00 Max Kremer <mkr...@trialfire.com <javascript:>>
> :
>
>> 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 <mkr...@trialfire.com>:
>>>
>>>> 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 jooq-user+...@googlegroups.com.
>>>> 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 jooq-user+...@googlegroups.com <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 jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
