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] <javascript:>> 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] <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.
