Variable not defined in query

Neo4j Graph Platform
1

Hi,

What is the best solution to define the variable collectionRecords in the second part of the query ?

// Collect all cable data for each area
// Part 1
MATCH (cables:Cables)-[:PULLED_TO]->(area:Area)<-[:BELONGS_TO]-(areaDescription:AreaDescription)
WITH areaDescription.deck as deck,
areaDescription.blockNr as block,
area.fromLocation AS to,
count(cables.van_object) AS nrCables,
sum(cables.length) AS length
WITH {Deck: deck,
Block: block,
AreaTo: to,
NrCables: nrCables,
Length: length } as recordSet
ORDER BY recordSet.AreaFrom
WITH COLLECT(recordSet) as collectionRecords
//Part 2
UNWIND ['Main','Local'] as cableIdType
MATCH (cables:Cables{cableType:cableIdType})-[:PULLED_TO]->(area:Area)<-[:BELONGS_TO]-(areaDescription:AreaDescription)
WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
WITH cables.pulled AS cablePullType,
area.fromLocation AS to,
cableIdType,
count(*) as countOfCables,
sum(cables.length) AS sumOfLengths
WITH {
AreaTo: to,
CableType: cableIdType,
CablePullType: cablePullType,
NrOfCables: countOfCables,
LengthOfCables: sumOfLengths
} as typeData
ORDER BY typeData.AreaTo
WITH COLLECT(typeData) as collectionTypeData
RETURN collectionTypeData, collectionRecords

Variable collectionRecords not defined (line 34, column 28 (offset: 1231))
"RETURN collectionTypeData, collectionRecords"

Kind regards,
Robert

2

@r.kempers

though neo4j version detail has not been provided should be applicable for most all versions and

It is important to note that WITH affects variables in scope. Any variables not included in the WITH clause are not carried over to the rest of the query. The wildcard * can be used to include all variables that are currently in scope.

as such change

WITH COLLECT(typeData) as collectionTypeData
RETURN collectionTypeData, collectionRecords

to

WITH COLLECT(typeData) as collectionTypeData, collectionRecords
RETURN collectionTypeData, collectionRecords
3

Thanks for your answer Dana but that is not the solution.

With your suggestion it does have still the same result, "Variable collectionRecords not defined."
WITH COLLECT(typeData) as collectionTypeData, collectionRecords
RETURN collectionTypeData, collectionRecords

In my opinion is not so easy to solve.

4

@r.kempers

Using Neo4j 5.7.0 I am able to get the following to run and with no error. ( but note as you have provide no version details it is unclear if maybe there is a bug in some other version ) Do you have Neo4j version details?

MATCH (cables:Cables)-[:PULLED_TO]->(area:Area)<-[:BELONGS_TO]-(areaDescription:AreaDescription)
WITH areaDescription.deck as deck,
areaDescription.blockNr as block,
area.fromLocation AS to,
count(cables.van_object) AS nrCables,
sum(cables.length) AS length
WITH {Deck: deck,
Block: block,
AreaTo: to,
NrCables: nrCables,
Length: length } as recordSet
ORDER BY recordSet.AreaFrom
WITH COLLECT(recordSet) as collectionRecords
//Part 2
UNWIND ['Main','Local'] as cableIdType
MATCH (cables:Cables{cableType:cableIdType})-[:PULLED_TO]->(area:Area)<-[:BELONGS_TO]-(areaDescription:AreaDescription)
WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
WITH cables.pulled AS cablePullType,
area.fromLocation AS to,
cableIdType,
count(*) as countOfCables,
sum(cables.length) AS sumOfLengths, collectionRecords
WITH {
AreaTo: to,
CableType: cableIdType,
CablePullType: cablePullType,
NrOfCables: countOfCables,
LengthOfCables: sumOfLengths
} as typeData, collectionRecords
ORDER BY typeData.AreaTo
WITH COLLECT(typeData) as collectionTypeData, collectionRecords
RETURN collectionTypeData, collectionRecords
5

Since these two queries are not correlated, you can execute them as two subqueries. This would avoid passing the value through from the first query, through the second, to the result. In this case, I find it simpler to follow.

BTW: Your original query is sorting on recordSet.AreaFrom in Part 1, but I did not see that variable defined. I switched the sorting on AreaTo, which is in the data. You should also be able to sort just on 'to' in each case as well.

call {
    MATCH (cables:Cables)-[:PULLED_TO]->(area:Area)<-[:BELONGS_TO]-(areaDescription:AreaDescription)
    WITH areaDescription.deck as deck,
    areaDescription.blockNr as block,
    area.fromLocation AS to,
    count(cables.van_object) AS nrCables,
    sum(cables.length) AS length
    WITH {
        Deck: deck,
        Block: block,
        AreaTo: to,
        NrCables: nrCables,
        Length: length 
    } as record
    ORDER BY record.AreaTo
    RETURN COLLECT(record) as collectionRecords
}
call {
    UNWIND ['Main','Local'] as cableIdType
    MATCH (cables:Cables{cableType:cableIdType})-[:PULLED_TO]->(area:Area)<-[:BELONGS_TO]-(areaDescription:AreaDescription)
    WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
    WITH cables.pulled AS cablePullType,
    area.fromLocation AS to,
    cableIdType,
    count(*) as countOfCables,
    sum(cables.length) AS sumOfLengths
    WITH {
        AreaTo: to,
        CableType: cableIdType,
        CablePullType: cablePullType,
        NrOfCables: countOfCables,
        LengthOfCables: sumOfLengths
    } as typeData
    ORDER BY typeData.AreaTo
    RETURN COLLECT(typeData) as collectionTypeData
}
RETURN collectionRecords, collectionTypeData
1 Like
6

Gary,

Thanks for the example you showed me. Your solution is a great way to solve this kind of issues.

I'm working with a cable database, where cables are connected to equipment which located in an area which has an description.

image
image901×226 22.2 KB

I like to store cable data per area in the AreaDescription. I was looking for ways to collect the cable data and store it somewhere. for reporting. With the query below I'm able to collect all kind of cable data per area. This is working great. Now I'm wondering what is the best query solution to make the UNION for both record sets. Your way or my way. Your query looks easier to read. What is your opinion ?

// Collect cable information V0.2
Call { UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)<-[:HAVE_AD]-(area:Area)<-[:PULLED_TO]-(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       WITH area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType,
            count(*) as countOfCables, 
            sum(cables.length) AS sumOfLengths
       WITH {
             AreaLocation: location,
             CableType: cableIdType,
             CablePullType: cablePullType,
             NrOfCables: countOfCables, 
            LengthOfCables: sumOfLengths} as typeData
       RETURN typeData as recordSet
       UNION
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)-[:BELONGS_TO]->(area:Area)-[:PULLED_FROM]->(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       WITH area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType,
            count(*) as countOfCables, 
            sum(cables.length) AS sumOfLengths
       WITH {
             AreaLocation: location,
             CableType: cableIdType,
             CablePullType: cablePullType,
             NrOfCables: countOfCables, 
             LengthOfCables: sumOfLengths} as typeData
       RETURN typeData as recordSet
     }
WITH recordSet.AreaLocation as location,
     recordSet.CableType as cableType,
     recordSet.CablePullType as cablePullType,
     sum(recordSet.NrOfCables) as nrOfCables,
     sum(recordSet.LengthOfCables) as lengthOfCables
RETURN location, cableType, cablePullType, nrOfCables, lengthOfCables

Kind regards,
Robert