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
2015-03-23 15:55 GMT+01:00 Conor <[email protected]>:
> 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
>
> --
> 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.
