Hi Marty,
I will be assuming that your query is already correct, so here are a few
tips as to how you can simplify writing it. First off, I recommend
assigning an aliased table to a local variable like this:
Client root = CLIENT.as("root");
This way, you can now dereference columns from the aliased table like this
(your union all's first subquery):
select(root.ID, root.NAME,
root.PARENT_ID).from(root).where(root.ID.eq(clientId))
The same is true for the CLIENT.as("child") table. Next, you can apply the
same trick to your "clientlist" in order to dereference columns from it.
The advantage of this approach is that your query will be less verbose and
more type safe.
Finally, since you're not really renaming any of the original columns, you
can re-use the original columns to dereference columns from derived tables,
such as
cte.field(CLIENT.ID)
This will dereference the "clientlist"."id" column, assuming it has the
same type as CLIENT.ID
I hope this helps,
Lukas
On Mon, May 6, 2019 at 3:08 PM <[email protected]> wrote:
>
> Thanks for your reply Lucas.
>
> I was able to eventually get something to work. Here is what I came up
> with:
>
>
> CommonTableExpression<Record3<Object, Object, Object>> cte =
> name("clientlist").fields("id", "name", "parent_id").as(
> select(field(name("root", "id")), field(name("root", "name")),
> field(name("root", "parent_id"))).from(CLIENT.as("root"))
> .where(field(name("root", "id")).eq(clientId))
> .unionAll(
> select(field(name("child", "id")), field(name("child",
> "name")), field(name("child", "parent_id")))
> .from(CLIENT.as("child"),
> table("clientlist").as("parent"))
> .where(
> field(name("parent",
> "id")).eq(field(name("child", "parent_id")))
> .and(not(field(name("child",
> "id")).eq(-1))))));
>
> Select<?> selectQuery =
> dsl.withRecursive(cte).select(cte.field("id"),
> cte.field("name"), cte.field("parent_id")).from(cte).orderBy(1);
>
> I would appreciate any advice on improvements you can suggest.
>
> Marty
>
>
>
>
> On Monday, May 6, 2019 at 4:17:59 AM UTC-5, Lukas Eder wrote:
>>
>> Hi Marty,
>>
>> Thanks for your message. What are you struggling with specifically?
>>
>> Thanks,
>> Lukas
>>
>> On Thu, May 2, 2019 at 6:35 PM <[email protected]> wrote:
>>
>>> All,
>>>
>>> I have been trying to construct DSL Select for the following SQL string:
>>>
>>> WITH RECURSIVE
>>> clientlist (ID, NAME, PARENT_ID) AS
>>> (
>>> SELECT root.ID, root.NAME, root.PARENT_ID FROM ACP.CLIENT root
>>> WHERE
>>> root.ID = ?
>>> UNION ALL
>>> SELECT child.ID, child.NAME, child.PARENT_ID FROM ACP.CLIENT child,
>>> clientlist parent
>>> WHERE parent.ID = child.PARENT_ID AND NOT child.ID = -1
>>> )
>>> SELECT ID, NAME, PARENT_ID FROM clientlist ORDER BY 1
>>>
>>> and have been unable to do it.
>>>
>>> This is for a Postgres DB.
>>>
>>> (Assume the ACP.CLIENT table has columns ID (Integer), NAME
>>> (varchar(64), PARENT_ID (Integer) )
>>>
>>> Any help will be greatly appreciated.
>>>
>>> Thanks,
>>> Marty
>>>
>>> --
>>> 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.
>>>
>> --
> 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.
>
--
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.
