jooq CommonTableExpression recursive

[Amit Roy]

I'm trying to write the following query is jooq and so far not having success. Getting a run time exception, where I see that the JOOQ generated sql is not matching my original one.

My SQL

==========

final String sql = WITH RECURSIVE getCommand(id, parent_id) AS(

SELECT id, parent_id FROM command WHERE id='some_val' 

UNION 

SELECT aliasedCmd.id, aliasedCmd.parent_id FROM getCommand cmd, command aliasedCmd WHERE aliasedCmd.id = cmd.parent_id) 

SELECT * FROM getCommand;

==========

The equivalent Jooq representation

==========

    xxx.jooq.internal.tables.Command cmd = COMMAND.as( "cmd" );

    xxx.jooq.internal.tables.Command aliasedCmd = COMMAND.as( "aliasedCmd" );

    CommonTableExpression<Record2<String,String>> getCommand = name("getCommand")

        .fields("id","parent_id")

        .as(create

            .select(cmd.ID, cmd.PARENT_ID)

            .from(cmd)

            .where(cmd.ID.eq("some_val"))

        .union(create

                .select(aliasedCmd.ID, aliasedCmd.PARENT_ID)

                .from(aliasedCmd)

                .where(aliasedCmd.ID.eq(cmd.PARENT_ID))));

    Result<?> obj = create.with(getCommand).select().fetch();

==========

The run time error message

==========

org.jooq.exception.DataAccessException: SQL [with "getCommand"("id", "parent_id") as ((select "cmd"."id", "cmd"."parent_id" from "internal"."command" as "cmd" where "cmd"."id" = ?) union (select "aliasedCmd"."id", "aliasedCmd"."parent_id" from "internal"."command" as "aliasedCmd" where "aliasedCmd"."id" = "cmd"."parent_id")) select 1]; ERROR: missing FROM-clause entry for table "cmd"

  Position: 264

==========

Thanks,

Amit.

[Amit Roy]

In the section "The equivalent Jooq representation", there is a typo.

Please read "Result<?> obj = create.with(getCommand).select().fetch();" as "Result<?> obj = create.withRecursive(getCommand).select().fetch();"

The run time error message as a result changes to

===========

org.jooq.exception.DataAccessException: SQL [with recursive "getCommand"("id", "parent_id") as ((select "cmd"."id", "cmd"."parent_id" from "internal"."command" as "cmd" where "cmd"."id" = ?) union (select "aliasedCmd"."id", "aliasedCmd"."parent_id" from "internal"."command" as "aliasedCmd" where "aliasedCmd"."id" = "cmd"."parent_id")) select 1]; ERROR: missing FROM-clause entry for table "cmd"

  Position: 274

===========

[Lukas Eder]

It seems you're simply missing (as the error helpfully indicates 😉) the "cmd" table in your union's second subquery. Add

.from(aliasedCmd, cmd)

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

[Amit Roy]

Thanks Lukas for pointing out the issue. Now, it's not throwing exception but the result is different from the original query. The jooq generated query is slightly different from the original one. I'm copying the original query, jooq expression and the jooq generated query.

My SQL

==========

final String sql = WITH RECURSIVE getCommand(id, parent_id) AS

(SELECT id, parent_id FROM command WHERE id='some_val' 

UNION 

SELECT aliasedCmd.id, aliasedCmd.parent_id FROM getCommand cmd, command aliasedCmd WHERE aliasedCmd.id = cmd.parent_id) 

SELECT * FROM getCommand;

==========

The equivalent Jooq representation

==========

    *...jooq.internal.tables.Command cmd = COMMAND.as( "cmd" );

    *...jooq.internal.tables.Command aliasedCmd = COMMAND.as( "aliasedCmd" );

    CommonTableExpression<Record2<String,String>> getCommand = name("getCommand")

        .fields("id","parent_id")

        .as(create

            .select(cmd.ID, cmd.PARENT_ID)

            .from(cmd)

            .where(cmd.ID.eq("some_val"))

        .union(create

                .select(aliasedCmd.ID, aliasedCmd.PARENT_ID)

                .from(aliasedCmd, cmd)

                .where(aliasedCmd.ID.eq(cmd.PARENT_ID))));

    Result<?> obj = create.withRecursive(getCommand).select().from(getCommand).fetch();

==========

The jooq generated query

==========

with recursive "getCommand"("id", "parent_id") as ((select "cmd"."id", "cmd"."parent_id" from "internal"."command" as "cmd" where "cmd"."id" = ?) union (select "aliasedCmd"."id", "aliasedCmd"."parent_id" from "internal"."command" as "aliasedCmd", "internal"."command" as "cmd" where "aliasedCmd"."id" = "cmd"."parent_id")) select "getCommand"."id", "getCommand"."parent_id" from "getCommand"

==========

Amit.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Amit Roy]

essentially, in the second union I cannot reference getcommand, which I'm doing in the SQL.

[Lukas Eder]

Yes of course, my bad. You should write:

    *...jooq.internal.tables.Command cmd = COMMAND.as( "cmd" );

    *...jooq.internal.tables.Command aliasedCmd = COMMAND.as( "aliasedCmd" );

    CommonTableExpression<Record2<String,String>> getCommand = name("getCommand")

        .fields("id","parent_id")

        .as(create

            .select(cmd.ID, cmd.PARENT_ID)

            .from(cmd)

            .where(cmd.ID.eq("some_val"))

        .union(create

                .select(aliasedCmd.ID, aliasedCmd.PARENT_ID)

                .from(aliasedCmd, name("getCommand").as("cmd"))

                .where(aliasedCmd.ID.eq(cmd.PARENT_ID))));

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Amit Roy]

In name("getCommand").as("cmd") - name returns Name and as() takes a SELECT not a string or a table. So, the solution won't compile. 

[Lukas Eder]

What I meant was table(name("getCommand")).as("cmd")

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.