Recursive CTE as a join table

[adamzrk]

Hi
I have situation where my objects are stored in a parent-child relation, but pointer to the parent is not stored in the object itself but in the collection
The object look like this:

@entity
`class A { // root of hierarchy
@id
long id;
}

@entity
class B {
private RelationToA A
}

@entity
class C {
private RelationToB relationToB;
private RelationToC relationToC;
}
`
RelationToA and RelationToC are intermediate classes with value of related object's identifier.
Class A can have many B and B might have many C. C can have one parent C. So it might look like A -> B -> C1 -> C2 -> C3 -> C4

The problem is when doing some query based on some criteria I have collection of C objects returned but during the query execution I have to check some property of the root of whole hierarchy - the A object. So my query looks like:

`

with recursive parent_hierarchy as (
select crc.reference_id as parent, c.id as child
from C c
left join c_relations_c crc on c.id = crc.ct_id
where c.id = :cId
union all
select crc.reference_id as parent, c.id as child
from C c
left join c_relations_c crc on c.id = crc.c_id
join parent_hierarchy hierarchy on c.id = hierarchy.parent
)
select a.id
from C c
join c_relations_B crb on c.id = crb.c_id
join B f on f.id = crb.reference_id
join B_relations_A bra on f.id = bra.b_id
join A a on a.id = bra.a_id
where c.id in (select child from parent_hierarchy where parent is null);

`

How to do that in Blaze? I have all the criterias for my main query for selecting Cs, but when I need to get my A root I am confused.
Ideally would be to have the query I pasted mapped as an entity and do join but there's parameter in the query for a C object.
What parts of the Blaze framework should I use to achieve what I want?

[beikov]

One way you could do this is by looking for the "root C" i.e.

with recursive parent_hierarchy(c_id, b_id, a_id) as (
  select c.id, c.b.id, c.b.a.id
  from C c where relationToC is null
  union all
  select c.id, h.b_id, h.a_id
  from C c 
  join parent_hierarchy h on h.c_id=relationToC.id
)
select * from parent_hierarchy

[adamzrk]

Thanks for reply - but how having c.id from the bottom of the hierarchy use it? And how to map it into hibernate/blaze code?

[beikov]

You create a CTE entity as described in the documentation and formulate the query with the operations of the Blaze-Persistence query builder. I don't know how you want to use it, but you can select the whole hierarchy in one go and then build a tree however you want/need in your Java code from that data.