Reduce code in Cypher

[r.kempers]

Hi,

Is there a possibility to reduce code because as a software engineer I don't like the code structure below. The only difference is "Local" instead of "Main".

// Count and define length all Main cables which are identified as Non Case-insensitive SPARE
MATCH (cables:Cables)
MATCH (cableInfo:CableInformation)
// Non Case-insensitive
WHERE cables.Omschrijvingen =~ '(?i).SPARE.' AND cables.cableType = "Main"
WITH count(cables) AS nrSpareCablesMain,
sum(cables.Lengte) AS lengthSpareCablesMain,
cableInfo
SET cableInfo.nrOfSpareCablesMain = nrSpareCablesMain,
cableInfo.lengthOfSpareCablesMain = lengthSpareCablesMain;

// Count and define length all Local cables which are identified as Non Case-insensitive SPARE
MATCH (cables:Cables)
MATCH (cableInfo:CableInformation)
// Non Case-insensitive
WHERE cables.Omschrijvingen =~ '(?i).SPARE.' AND cables.cableType = "Local"
WITH count(cables) AS nrSpareCablesMain,
sum(cables.Lengte) AS lengthSpareCablesLocal,
cableInfo
SET cableInfo.nrOfSpareCablesLocal = nrSpareCablesMain,
cableInfo.lengthOfSpareCablesLocal = lengthSpareCablesLocal;

Thanks in advance

[john.stegeman (John "Steggy" Stegeman)]

Hi @r.kempers

Have you read about variables in Cypher?

Parameters - Cypher Manual

[glilienfield (Gary Lilienfield)]

I don't understand your match to CableInformation node upfront. There are no constraints, so you are going get every CableInformation node. The effect will be the Cartesian product between the two matches, so if you have 'n' CableInformation nodes, you will get each Cables node repeated 'n' times. This should give you incorrect aggregate results for 'sum' and' count'. There will be no impact if you have just one CableInformation node.

I moved the match on the CableInformation node to a merge at the end to save the aggregate data. The is one CableInformation node for each type. Alter this part to meet your needs. I compacted the code by searching for either value of type, and then including the type in the 'with' clause, so the aggregations are done over the rows for each type. Hopefully this will demonstrate what you want so you can alter it to meet your needs.

MATCH (cables:Cables)
WHERE cables.Omschrijvingen =~ '(?i).SPARE.' AND cables.cableType IN ["Main", "Local"]
WITH cables.cableType as type, count(cables) AS nrSpareCablesMain, sum(cables.Lengte) AS lengthSpareCablesMain
MERGE (cableInfo:CableInformation{type: type})
SET cableInfo.nrOfSpareCablesMain = nrSpareCablesMain,
cableInfo.lengthOfSpareCablesMain = lengthSpareCablesMain

[r.kempers]

Gary,

I understand you don't understand my match to CableInformation node upfront. Its my fault because I test the query with declaring each time the CableInformation. Fully understand your remarks.

Again here is some learning curve.
By the way I didn't test the query as well so that's why its confusing as well.

My original question is can I reduce some code clauses. I'm doubting it will be.
Because of the query structure the "Main" and "Local" are the same. Just wondering if I can reduce some clauses. Depeding on the cable.cableType some variables (main or local) will be calculated.

1. The correct query should be:
   // Count and define length all Main cables which are identified as Non Case-insensitive SPARE
   MATCH (cables:Cables)
   // Non Case-insensitive
   WHERE cables.Omschrijvingen =~ '(?i).SPARE.' AND cables.cableType = "Main"
   WITH count(cables) AS nrOfSpareCablesMain,
   sum(cables.Lengte) AS lengthSpareCablesMain,
   cableInfo
   SET cableInfo.nrOfSpareCablesMain = nrOfSpareCablesMain,
   cableInfo.lengthSpareCablesMain = lengthSpareCablesMain;

// Count and define length all Local cables which are identified as Non Case-insensitive SPARE
MATCH (cables:Cables)
// Non Case-insensitive
WHERE cables.Omschrijvingen =~ '(?i).SPARE.' AND cables.cableType = "Local"
WITH count(cables) AS nrOfSpareCablesLocal,
sum(cables.Lengte) AS lengthSpareCablesLocal,
cableInfo
SET cableInfo.nrOfSpareCablesLocal = nrOfSpareCablesLocal,
cableInfo.lengthSpareCablesLocal = lengthSpareCablesLocal;

With your advice I have changed the code into:
//Example
// 01. Delete all
MATCH (all) DETACH DELETE all;
// Delete record cableInfo first, to be sure we have only one record.
MATCH (cableInfo:CableInformation) DETACH DELETE cableInfo;

CREATE (cable1:Cables {length:100, cableType:"Main", description:"[Lt-Supply][P]LP7421 Supply 2 Lighting"});
CREATE (cable2:Cables {length:100, cableType:"Main", description:"[Crew-Lt][P]Art Light (Spare)"});
CREATE (cable3:Cables {length:100, cableType:"Main", description:"[RZ][1]24VDC GMDSS Light Red (SPARE)"});
CREATE (cable4:Cables {length:100, cableType:"Main", description:"[Tech-Lt][P]Power Socket"});
CREATE (cable1:Cables {length:100, cableType:"Local", description:"[Lt-Supply][P]LP7421 Supply 2 Lighting"});
CREATE (cable2:Cables {length:100, cableType:"Local", description:"[Crew-Lt][P]Art Light (Spare)"});
CREATE (cable3:Cables {length:100, cableType:"Local", description:"[RZ][1]24VDC GMDSS Light Red (SPARE)"});
CREATE (cable4:Cables {length:100, cableType:"Local", description:"[Tech-Lt][P]Power Socket"});

// Create a local node to store data
// In my opnion easy to use in a dashboard for instance NeoDash
MERGE (cableInfo:CableInformation {nrOfCablesMain: 0,
lengthCablesMain: 0,
nrOfCablesLocal: 0,
lengthCablesLocal: 0,
nrOfSpareCablesMain: 0,
lengthOfSpareCablesMain: 0,
nrOfSpareCablesLocal: 0,
lengthOfSpareCablesLocal: 0
}
);

MATCH (cables:Cables)
WHERE cables.cableType IN ["Main", "Local"] // AND cables.description =~ '(?i).SPARE.'
WITH cables.cableType as type,
cables,
count(cables) AS nrOfSpareCablesMain,
sum(cables.length) AS lengthOfSpareCablesMain,
count(cables) AS nrOfSpareCablesLocal,
sum(cables.length) AS lengthOfSpareCablesLocal
CALL apoc.do.when(type="Main",
"count(cables) AS nrOfSpareCablesMain
sum(cables.length) AS lengthOfSpareCablesMain
RETURN 1 as result",
"count(cables) AS nrOfSpareCablesLocal
sum(cables.length) AS lengthOfSpareCablesLocal
RETURN 2 as result",
{cables:cables}) YIELD value
MERGE (cableInfo:CableInformation{type: type})
WITH cables.cableType as type,
nrOfSpareCablesMain,
lengthOfSpareCablesMain,
nrOfSpareCablesLocal,
lengthOfSpareCablesLocal,
value,
cableInfo
CALL apoc.do.when(value.result = 1,
"cableInfo.nrOfSpareCablesMain = nrOfSpareCablesMain
cableInfo.lengthOfSpareCablesMain = lengthOfSpareCablesMain
RETURN 1 as result",
"cableInfo.nrOfSpareCablesLocal = nrOfSpareCablesLocal
cableInfo.lengthOfSpareCablesLocal = lengthOfSpareCablesLocal
RETURN 2 as result",
{cableInfo:cableInfo}) YIELD value1
RETURN value1.result

I don't know if this query is the right one. As far I know something is wrong with "YIELD value1". I don't understand why.
The other remark is, I comment out "AND cables.description =~ '(?i).SPARE.'" because it doesn't work. Don't understand because the query at 1) is working correctly.

Can you please help me.

Thanks in advance.

[glilienfield (Gary Lilienfield)]

This seems overly complicated. One mistake that must be giving you incorrect results is the inclusion of 'cables' on your first 'with' clause. This causes the aggregations of 'sum' and 'count' to be done over each individual 'cables' node. When you have the following line, the rows are grouped by 'type' and the aggregation functions are calculated for each group. As a result, you will have one row for each value of type, with the 'sum' and 'count' values for that type included on the line.

WITH cables.cableType as type,  count(cables) AS nrOfSpareCables, sum(cables.length) AS lengthOfSpareCables

Result without grouping (each row is a Cables node):

Result with grouping (each row has the aggregate stats for one type):

Since you stated you wanted to display the results in NeoDash, I chose to use apoc virtual nodes to show their advantages for visualization. These nodes are not persisted, but returned in the query results for the purpose of displaying them. The following query calculates the stats for the two types of cables and creates a CableInformation virtual node for each type with the aggregated values as properties.

MATCH (cables:Cables)
WHERE cables.cableType IN ["Main", "Local"]
WITH cables.cableType as type, count(*) as countOfCables, sum(cables.length) as sumOfLengths
WITH {type: type, nrOfSpareCables: countOfCables, lengthOfSpareCables: sumOfLengths} as typeData
CALL apoc.create.vNode(['CableInformation'], typeData) yield node
RETURN node

[r.kempers]

@glilienfield

MATCH (cables:Cables)
WHERE cables.cableType IN ["Main", "Local"]
WITH cables.cableType as type, count(*) as countOfCables, sum(cables.length) as sumOfLengths
WITH {type: type, nrOfSpareCables: countOfCables, lengthOfSpareCables: sumOfLengths} as typeData
CALL apoc.create.vNode(['CableInformation'], typeData) yield node
RETURN node

Gary,
You are definitely a Neo4j Ninja !
Was a few days off but this solution helps me a lot.
I learnt how to compromise queries in some small logical queries and the use of virtual nodes.
Will certainly use it in my project.
The only challenge left is to add two extra virtual nodes for the "Spare" cables Main and Local.
First I will try to figure it out by myself.

Kind regards Robert