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.
