OrientDB traverse from variable

Question

I have this graph in OrientDB 3.2.14:

A user (Usuario) can belong to a UnidadOrganizativa that could be an Oficina, Dpto, UnidadFiscal or FiscaliaRegional. The hierarchy is: Oficina <--- Dpto <--- UnidadFiscal <--- FiscaliaRegional.

I need to list every Legajo with the UnidadFiscal and this query should work but throws an error:

select fechaCreacion.format('yyyyMM') as name,
       $uo as uo,
       $uf  as UF   
from Legajo
    Let $uo = $current.out('Legajo_responsable').out('Fiscal_usuario').out('Usuario_unidadFiscal')[jerarquia != "FISCALIA_GENERAL"][0],
        $uf = (traverse in('UnidadOrganizativa_dependencias') from $parent.uo while jerarquia != "UNIDAD_FISCAL")
where fechaCreacion >= '2022-01-01' and fechaCreacion <= '2022-12-31' limit 10;

When I run it Orient say:

Class $parent not found DB name="heimdall-20230801"

I try differents way but nothing work. Any reference to $parent and $current inside a subquery do not work.

In the query $uo have a correct value but I need to get the UnidadFiscal that the UO belong to if it is not one.

Answer 1

As I understand from the comments below, you need to find all the 'Legajo' vertices with the last 'UnidadOrganizative' vertex in the chain from the Legajo.

The logic for the query would have to be in reverse, i.e. from Org to Legajo, not other way round, first, find all the top level UnidadOrgs, i.e. the UnidadOrgs that does not have any other '..._depen' edges coming in.

SELECT FROM UnidadOrganizative where in('..._depen')=null or in('..._depen').size() = 0

Refer: This question

Now we have the top level UnidadOrgs vertices, traversal to find the Legajo needs to start backwards.

SELECT FROM (TRAVERSE out('..._depen'), in('Usurari...'), in('Fiscal_'), in('legajo_...') FROM (SELECT FROM UnidadOrganizative WHERE in('..._depen')=null or in('..._depen').size() = 0)) WHERE (@class='Lehajo' OR @class='UnidadOrganizative') AND $depth>0

The above query will find all the UnidadOrganizative (even intermediate ones, which we don't want) and Legojo. The $depth>0 eliminates the UnidadOrganizative start vertex (in traversal, the start vertex always is returned, we don't want that).

Next the vertices need to be restricted to the first vertex in the traversal chain and the last vertex in the traversal chain. The WHERE clause has been moved out to the outer most query

SELECT traversedElement(0), traversedElement(-1) FROM (SELECT FROM (TRAVERSE out('..._depen'), in('Usurari...'), in('Fiscal_'), in('legajo_...') FROM (SELECT FROM UnidadOrganizative WHERE in('..._depen')=null or in('..._depen').size() = 0))) WHERE (@class='Lehajo' OR @class='UnidadOrganizative') AND $depth>0

traversedElement(0) is the first element of the traversal chain in this case 'UnidadOrganizative' and traversedElement(-1) is the last element of traversal chain, in this case Legajo.

Refer: OrientDB Doc - Functions

The final output will look something like the below image. It contains only the rids

To get the property values, the specific properties of the elements need to be mentioned (it is not possible to use the expand() function to expand all the properties).

For example, the below could be done traversedElement(0).name AS OrgName, traversedElement(0).name AS Legajo.name etc. to get the specific properties.

I have tested a similar query in my environment but my graph structure is slightly different from yours. So I am not 100% sure the above query will work in your environment, you may need to modify it. However, the logic remains the same.