DQL: getting called services nested

[erh_inetum]

Hi,

I'm trying to get the called services nested from the other one. I want to get something like this using DQL.

For this I'm launching this query:

fetch dt.entity.service
|filter entity.name == "MY SERVICE"
|fieldsAdd entity.name
|fieldsAdd called_by
|filter isNotNull(entity.name)
|expand service1 = called_by[dt.entity.service]
|fieldsAdd service1name = lookup([fetch dt.entity.service],
sourceField:service1, lookupField:id)[entity.name]
| join [fetch dt.entity.service | filter entity.name == "SERVICE CALLED BY MY SERVICE"|fieldsAdd entity.name|fieldsAdd called_by|filter isNotNull(entity.name)|expand service2 = called_by[dt.entity.service]|fieldsAdd service2name = lookup([fetch dt.entity.service],sourceField:service2, lookupField:id)[entity.name]],
on: {entity.name}

but it isn´t return results. But if I lauch these queries separately, I get the expected results.

It seems service2 information is not being collected. Because if I launch this:

fetch dt.entity.service
|filter entity.name == "MY SERVICE"
|fieldsAdd entity.name
|fieldsAdd called_by
|filter isNotNull(entity.name)
|expand service1 = called_by[dt.entity.service]
|fieldsAdd service1name = lookup([fetch dt.entity.service],
sourceField:service1, lookupField:id)[entity.name]
| join [fetch dt.entity.service | filter entity.name == "SERVICE CALLED BY MY SERVICE"|fieldsAdd entity.name|fieldsAdd called_by|filter isNotNull(entity.name)|expand service2 = called_by[dt.entity.service]|fieldsAdd service2name = lookup([fetch dt.entity.service],sourceField:service2, lookupField:id)[entity.name]],
on: {entity.name}
| fieldsAdd hierarchy = concat(service1, " -> ", service2)
| filter isNotNull(service1name) and isNotNull(service2name)

I get the error 

{
"error": {
"message": "FIELD_DOES_NOT_EXIST",
"details": {
"exceptionType": "DQL-RESULT_TYPE",
"syntaxErrorPosition": {
"start": {
"column": 50,
"index": 803,
"line": 11
},
"end": {
"column": 57,
"index": 810,
"line": 11
}
},
"errorType": "FIELD_DOES_NOT_EXIST",
"errorMessage": "The field service2 doesn't exist.",
"arguments": [
"service2"
],
...

...
],
"errorMessageFormat": "The field %1$s doesn't exist.",
"queryId": "253effe1-57be-4032-989e-e6937afafcad"
},
"code": 400
}
}

What am I doing wrong?

Thanks a lot in advance.

Regards,

Elena.

[Tiit_Hallas · Accepted solution]

Do you mean something like this?

fetch dt.entity.service
| fieldsAdd entity.name, 
            called_by = called_by[dt.entity.service], 
            calls = calls[dt.entity.service]
| filter isNotNull(entity.name)
| expand called_by
| expand calls
| filterOut isNull(called_by) or isNull(calls)
| fields hierarchy = concat(entityName(called_by, type:"dt.entity.service"), " => ", entityName(calls, type: "dt.entity.service"))

[erh_inetum]

Hi @Tiit_Hallas ,

Yes, this is that I need.

Thank you very much 🙂

Regards,

Elena