Struggling with Recursive CTE

[Conor]

Hi,

I am trying to create a query that makes use of WITH RECURSIVE in Postgres.

My problem is I can't figure out how to join onto itself.

This is the SQL I am trying to replicate.

WITH RECURSIVE entity_tree
AS (

    SELECT n.id as id, n.path, n.entity, n.owner

        FROM entity AS n

        WHERE n.entity in (SELECT * FROM entityIds)

    UNION ALL

    SELECT c.id, c.path, c.entity, c.owner

        FROM entity_tree AS p

        INNER JOIN entity AS c

            ON c.entity = p.id

)

Here is my Java

dtoEntity recursiveEntity = ENTITY.as( "n" );

dtoEntity subEntity = ENTITY.as( "p" );

CommonTableExpression<Record4<Long,String,Long,Long>> entityTree = name("entity_tree").fields("id","path","entity","owner")
.as(
    create.select( recursiveEntity.ID, recursiveEntity.PATH, recursiveEntity.ENTITY_, recursiveEntity.OWNER )
        .from( recursiveEntity )
        .where( recursiveEntity.ENTITY_.in( create.select( ENTITY.ID ).from( entityIds ) )
)
.unionAll(
    create.select( recursiveEntity.ID, recursiveEntity.PATH, recursiveEntity.ENTITY_, recursiveEntity.OWNER )
    .from( "entity_tree" ).join( subEntity ).on( subEntity.ENTITY_.eq( ??? ) )
);

I figure my problem is either something simple that I'm missing or I am going about it completely wrong.

Thanks

Conor

[Lukas Eder]

Hello,

Hmm, yes. It doesn't look as though your SQL version and your jOOQ version really correspond to each other. For instance, in your SQL version, in the second UNION ALL subselect, you're renaming the CTE from "entity_tree" to "p" and the original table from "entity" to "c".

But in the jOOQ version, you're selecting from "entity_tree" (not renamed) and from "entity" as "p", but you're projecting columns from "n", which is not in the FROM clause of that second UNION ALL subselect.

I suspect that this query would be the intended one:

dtoEntity n = ENTITY.as( "n" );

dtoEntity c = ENTITY.as( "c" );

CommonTableExpression<Record4<Long,String,Long,Long>> entityTree = name("entity_tree")

   .fields("id", "path", "entity", "owner")

   .as(create

       .select( n.ID, n.PATH, n.ENTITY_, n.OWNER )

       .from( n )

       .where( n.ENTITY_.in( create.select( ENTITY.ID ).from( entityIds ) )

   )

   .unionAll(create

       .select( c.ID, c.PATH, c.ENTITY_, c.OWNER )

       .from( tableByName("entity_tree").as("p") )

       .join( c ).on( c.ENTITY_.eq( fieldByName(<type here>, "p", "id") ) )

   );

It's a bit of a challenging situation for the jOOQ API, agreed. The "entity_tree" table is not yet really declared when it is referenced in the subquery. I suspect that for your specific use-case, there is room for improvement, namely because "n", "p", "c" all have the same type, except that "p" is not the "entity" table, but the "entity_tree" table...

I hope this already helped a bit. If you have any further questions regarding jOOQ's CTE API, or even better, suggestions about how to improve it, I'll happily hear them!

Cheers,
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.