Get a variable outside FOREACH

[vence_andersen1]

With reference to the above image, I want the variable "o" outside of the FOREACH loop. Hence I tried to go with apoc.do.when(below image)

The problem here is the now line is not recoganized as a variable in the last FOREACH loop, tried using WITH didn't help. Is there any way to do it?
Line is the reference for the CSV file I'm importing.

Thanks in advance.

[glilienfield · Accepted solution]

I guess the 'forEach' clause does not have access to the outer scope. As an alternative, maybe using 'Call' subqueries can do the same. The below is a snippet of code. Assuming 'n' and 'line' are defined in the outer scope of the 'Call' queries, how about this:

call {
    with line, n
    with line, n
    where line.`Issue Name` is not null 
    and line.DataField_2 is null

    merge (o:Issue{Name:line.`Issue Name`, Severity:line.`Issue Severity`})
    merge (n)-[:issue]-(o)
    return o
}

call {
    with line, o
    with line, o
    where line.`Recommendation Class` is not null
    and line.`Risk Name` is null

    merge (q:Recommendation{Name:line.`Recommendation Class`, Severity:line.`Recommendation Severity`})
    merge (o)-[:reco]-(q)
    return q
}

[vence_andersen1]

That really helped a lot, continuing to that id it possible to give a if else like situtaion?

call {   
    with line, o, p
    with line, o, p
    where line.`Recommendation Class` is not null
    and line.`Risk Name` is null

    merge (q:Recommendation{Name:line.`Recommendation Class`, Severity:line.`Recommendation Severity`})
    merge (o)-[:reco]-(q)
    return q
    
    with line, p,o
    with line, p,o
    where line.`Risk Name` is not null 
    and line.`Recommendation Class` is not null

    merge (q:Recommendation{Name:line.`Recommendation Class`, Severity:line.`Recommendation Severity`})
    merge (p)-[:risk]-(q)
}

The error I am getting here is "line" is not recoganized as a variable at 2nd condition inside call.

Thanks in advance.

[glilienfield]

One approach you could use for an ‘else’ condition is to have another ‘call’ sub query with else condition.

The ‘call’ clauses are not shown in the code you pasted. Can you paste the full section of code?

Thanks.

[vence_andersen1]

Sorry, I've updated the code with "call", is that what you asked.

[glilienfield]

I see. You have removed the second ‘call’ statement. The purpose of having two ‘call, clause is that you can have them execute regardless of the result the each other. When you have code in one block, the query will stop once a result is not generated.

[vence_andersen1]

That is because when I have the query like this,

call {   
    with line, o, p
    with line, o, p
    where line.`Recommendation Class` is not null
    and line.`Risk Name` is null

    merge (q:Recommendation{Name:line.`Recommendation Class`, Severity:line.`Recommendation Severity`})
    merge (o)-[:reco]-(q)
    return q
}
call
    with line, p,o
    with line, p,o
    where line.`Risk Name` is not null 
    and line.`Recommendation Class` is not null

    merge (q:Recommendation{Name:line.`Recommendation Class`, Severity:line.`Recommendation Severity`})
    merge (p)-[:risk]-(q)
    return q  //error line
}

I'm getting the error "Variable q already declared in outer scope" pointing the last "return q"

[glilienfield]

That makes sense. You can not return a value from a sub query the exists in the outer scope. The subquery return result or results get appended to the outer scope record. If you want to return both results with the same binding, then try converting the two subquery calls to one as a union.

[vence_andersen1]

Yeah tried that before, got an error "All sub queries in an UNION must have the same column names".

[vence_andersen1]

Can you please explain the issue here. The below code works perfectly

load csv with headers from "file:///data.csv" as line
merge (n:Df1{Name: line.`DataField_1 Name`, Class:line.`DataField_1 Class`})
call{
    with line, n
    with line, n
    
    where line.`Issue Name` is not null 
    and line.DataField_2 is null

    merge (o:Issue{Name:line.`Issue Name`, Severity:line.`Issue Severity`})
    merge (n)-[:issue]-(o)
    return o
}
return n

But if I add a call below that something like,

load csv with headers from "file:///data.csv" as line
merge (n:Df1{Name: line.`DataField_1 Name`, Class:line.`DataField_1 Class`})

WITH line, n
call {
    with line, n
    with line, n
    where line.DataField_2 is not null

    merge (m:Df2{Name: line.`DataField_2 Name`, Class:line.`DataField_2 Class`})
    merge (n)-[:implies]-(m)
    return m
}
call{
    with line, n
    with line, n
    
    where line.`Issue Name` is not null 
    and line.DataField_2 is null

    merge (o:Issue{Name:line.`Issue Name`, Severity:line.`Issue Severity`})
    merge (n)-[:issue]-(o)
    return o
}
return n

Anything after the first call is not generated, like Df2 nodes are created but Issue nodes are not getting created, so if I change the order like bringing the call for creating issue nodes to the top, then Issue nodes are created but the Df2 nodes are not getting created.

If you can share some reference here would also be helpful. Thanks in advance.

[glilienfield]

That is true, but your two queries where returning the same variable, which was q. That is why I thought the union would work.

Do you want them to be the same binding? By doing so, you will get either one or two results rows back for each outer query result. If you use different bindings, the results of the subquery will be appended to the outer query result row. Your first query had two different bindings. What is the result your want?

I will be glad to look at this later.

[glilienfield]

Try removing the return statements in the subqueries. You are not using the values in the outer query, so they are no necessary. More importantly, the results from a subquery are appended to the outer query. If the subquery has a return and the subquery has no result, the outer query will stop and also have no result.

Neo4j Graph Data Platform

CALL {} (subquery) - Neo4j Cypher Manual

The `CALL {}` clause evaluates a subquery that returns some values.

[vence_andersen1]

glilienfield:

If the subquery has a return and the subquery has no result, the outer query will stop and also have no result.

But the subquery returns nodes, yet still the call statement next to those are not working.

call{  //1st condition
this works and returns some nodes
}
call{  //2nd condition
this doesn't work
}

If I rearrage the order

call{  //2nd condition
this works and returns some nodes
}
call{  //1st condition
but this doesn't work
}

The query I used

Get a variable outside FOREACH Cypher

Can you please explain the issue here. The below code works perfectly load csv with headers from "file:///data.csv" as line merge (n:Df1{Name: line.`DataField_1 Name`, Class:line.`DataField_1 Class`}) call{ with line, n with line, n where line.`Issue Name` is not null and line.DataField_2 is null merge (o:Issue{Name:line.`Issue Name`, Severity:line.`Issue Severity`}) merge (n)-[:issue]-(o) return o } return n But if I add a call below that something like, lo…

[glilienfield]

The call subquery's results are appended to the outer query results, so when the subquery does not return a result, the outer query will stop and no results returned. So in your case, where each subquery is conditionally creating/returning results, the query will stop when either of the two 'line' properties you are checking are null; therefore, returning results from these subqueries will not work.

The following query should work, as there are no subquery return values; therefore, each subquery will execute and perform their merges when the 'where' conditions are satisfied.

load csv with headers from "file:///data.csv" as line
merge (n:Df1{Name: line.`DataField_1 Name`, Class:line.`DataField_1 Class`})

WITH line, n
call {
    with line, n
    with line, n
    where line.DataField_2 is not null

    merge (m:Df2{Name: line.`DataField_2 Name`, Class:line.`DataField_2 Class`})
    merge (n)-[:implies]-(m)
}
call{
    with line, n
    with line, n
    
    where line.`Issue Name` is not null 
    and line.DataField_2 is null

    merge (o:Issue{Name:line.`Issue Name`, Severity:line.`Issue Severity`})
    merge (n)-[:issue]-(o)
}

If you want the nodes created in the subqueries returned, then I think you will need to optionally match on them at the end.

load csv with headers from "file:///data.csv" as line
merge (n:Df1{Name: line.`DataField_1 Name`, Class:line.`DataField_1 Class`})

WITH line, n
call {
    with line, n
    with line, n
    where line.DataField_2 is not null

    merge (m:Df2{Name: line.`DataField_2 Name`, Class:line.`DataField_2 Class`})
    merge (n)-[:implies]-(m)
}
call{
    with line, n
    with line, n
    
    where line.`Issue Name` is not null 
    and line.DataField_2 is null

    merge (o:Issue{Name:line.`Issue Name`, Severity:line.`Issue Severity`})
    merge (n)-[:issue]-(o)
}

optional match (m:Df2{Name: line.`DataField_2 Name`, Class:line.`DataField_2 Class`})
optional match (o:Issue{Name:line.`Issue Name`, Severity:line.`Issue Severity`})

return n, m, o

I did some experimenting and found that the optional matches do not return values if any of the line properties are absent, so you will get a 'null' returned for 'm' and 'o' when they did not get created or didn't exists already.

You can also look a the apoc 'do.when' method:

Neo4j Graph Data Platform

apoc.do.when - APOC Documentation

This section contains reference documentation for the apoc.do.when procedure.

It was used in another thread I was involved in. You can see an example there:

:auto transaction doesn't run as described in the manual Cypher

@glilienfield @bennu.neo @Rcolinp Justr for your info, I loaded 11 million transactions of different kind, using the latest approach proposed by @glilienfield. This approach using apoc turned out to be by far the most readable one (after a bit of a sprucing up of the query!) This is the result: LOAD CSV WITH HEADERS FROM 'file:///transactions.csv' AS row FIELDTERMINATOR ';' CALL { WITH row WITH row WHERE row.transactionid IS NOT NULL MERGE (transaction:Transaction { transactionid : ro…