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.

Ironluca · Answer 1 · 2023-08-15T17:10:35.720

0

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.

edited Aug 15 '23 at 17:10

answered Aug 12 '23 at 15:00

Ironluca

3,402
4
25
32

Unfortunately it not resolve the traverse to the correct UO node. It take the first but not the correct. – Marcelo D. Ré Aug 14 '23 at 13:44
You require the UnidadOrg vertex or the Usuario edge that connect to the Unidad vertex? Cant you give the types or vertices/edges you require in the output. It would also hel if you can mark it on the diagram – Ironluca Aug 15 '23 at 04:47
I need to list every Legajo with the UnidadFiscal that is handle it, but if the Usuario is linked to an UO that is an Oficina I need to bubble up to get the correct UO. For example, if a case is handled by a user of the Internet Fraud Office that depend of Cyber Crime Department that depend of the Complex Crime Prosecutor Office (Unidad Fiscal), I need to list that file with the name of this last UO. – Marcelo D. Ré Aug 15 '23 at 12:17
Do you mean that the Unidad... vertices could be sometime 1, some time 2 and sometime 3. And you want to list the Legajo with the last Unidad... vertex in the chain? – Ironluca Aug 15 '23 at 14:08
yes. That's right. – Marcelo D. Ré Aug 15 '23 at 15:28
I have modified the answer based on the above comments. In this case MATCH will not work well as it requires you to know the depth. TRAVERSE is the way to go. I have used backward chaining as it may reduce unnecessary traversal and results in simpler query, forward chaining also can be used. – Ironluca Aug 15 '23 at 17:33
Unfortunately thats don't work. I tried diferents aproach but I don't find a way to do this. To clarify, theres 5 Regional (FR), 60 UF and 182 Office. At the same time, theres 2.5MM files and we only need to work with the records of 2022. That's 265.000 files. The file could be asigned to an Off, UF or FR. If a file is asigned to an Off we need to list the superior level, the UF Name. If you go back from UO to Files (Legajos) you get 2.5MM in traverse prior to filter and its too slow. – Marcelo D. Ré Aug 17 '23 at 12:25
The solution should be the use of $parent variable to work with traverse but it don't work. It seems theres no way to solve this in one query. Some time ago I writed a function to get the fullPath between to vertex. Now I will modify it to get a better traverse. – Marcelo D. Ré Aug 17 '23 at 12:25
Try using client API [https://orientdb.com/docs/last/java/Java-API.html], the API is available in multiple languages. That should help with the performance issue. – Ironluca Aug 17 '23 at 13:09

OrientDB traverse from variable

1 Answers1