Pruning results for cypher query in Neo4j Community edition 5.7.0

Neo4j Graph Platform Graph Algorithms/Graph Data Science
,
1

Hi, I have the below cypher query which is returning let's say 57 records. Each record is given a routeNumber, noOfLinks, totalCost. In the gds algorithm when I give the value as k: 5 I was expecting only 5 routes which could mean more than 5 records because each route can have more than 1 link.
Ex: A to Z routes could be something like below.

  1. A-Z (direct route. routeNumber 1: A-Z)
  2. A-B-Z (routeNumber 2: A-B, routeNumber 3: B-Z)
  3. A-C-D-Z
  4. A-C-E-Z
  5. A-D-F-Z

But my query is returning more records (after routeNumber 5 it's still returning more records and marking them from 1 to 5 again) which mean more processing time. What I'm looking for is if there is a way to let the algorithm give only 5 routes essentially no other records after the 1st 5 routeNumbers.

or if there is way to come up with generating the routeNumber based on other 2 columns noOfLinks, totalCost where the combination is supposed to be representing a single route for A to Z route. how do I achieve this? I tried LIMIT but it doesn't work because it's limiting the valid routes. All I need is records which marked as routeNumber from 1 to 5 based on the logic I explained above.

Cypher Query:

MATCH p = (n:EquipmentNode)-[Q:CONNECTED_TO]-(m:EquipmentNode) WHERE (Q.type IN ['TYPE1'] AND Q.availableChannels > 0 ) AND Q.networkId IN ['NETWORK1'] AND Q.status IN ['STATUS1'] WITH gds.graph.project( 'rakesh', n, m, { relationshipProperties: Q { .mileage, .isVirtualLinkHybrid }} ) as g MATCH (s:SiteNode)-[rl:CONNECTED_TO]-(a:EquipmentNode)-[rel:CONNECTED_TO]-(a1:EquipmentNode) WHERE (a.locationClli CONTAINS s.siteName OR s.siteName CONTAINS a.locationClli) AND s.meccaCode IN ['A'] AND toUpper(a.networkType)='ULH' AND not (toUpper(a.equipmentTid) contains 'TEST') AND rel.type IN ['TYPE1'] AND (rel.totalChannels > 0 and rel.totalChannelsUsed < rel.totalChannels) AND rel.networkId IN ['NETWORK1'] AND rel.status IN ['STATUS1'] WITH a, g MATCH (d:SiteNode)-[rl:CONNECTED_TO]-(z:EquipmentNode)-[rel:CONNECTED_TO]-(z1:EquipmentNode) WHERE (z.locationClli CONTAINS d.siteName OR d.siteName CONTAINS z.locationClli) AND d.meccaCode IN ['Z'] AND toUpper(z.networkType)='ULH' AND not (toUpper(z.equipmentTid) contains 'TEST') AND rel.type IN ['TYPE1'] AND (rel.totalChannels > 0 and rel.totalChannelsUsed < rel.totalChannels) AND rel.networkId IN ['NETWORK1'] AND rel.status IN ['STATUS1'] WITH DISTINCT z, a, g

CALL gds.shortestPath.yens.stream( 'rakesh', { sourceNode: a, targetNode: z, k:5, relationshipWeightProperty: 'mileage' })
YIELD index, sourceNode, targetNode, totalCost, nodeIds, costs, path
WITH index, sourceNode, targetNode, totalCost, costs, path, nodeIds, gds.util.asNode(sourceNode).nodeId AS startNode, gds.util.asNode(targetNode).nodeId AS endNode, gds.util.asNode(sourceNode).locationClli AS startLocation, gds.util.asNode(targetNode).locationClli AS endLocation, size(nodeIds)-1 AS noOfLinks, [nodeId IN nodeIds | gds.util.asNode(nodeId).locationClli] as clliInRoutes, [nodeId IN nodeIds | gds.util.asNode(nodeId).nodeId] AS tidsInRoutes
UNWIND relationships(path) as subPath
WITH index, sourceNode, targetNode, totalCost, costs, path, nodeIds, startNode, endNode, startLocation, endLocation, clliInRoutes, tidsInRoutes, noOfLinks, apoc.rel.startNode(subPath) AS subPathStartNode, apoc.rel.endNode(subPath) AS subPathEndNode, COLLECT(DISTINCT {noOfLinks: noOfLinks, totalCost: totalCost}) as uniqueRouteNumbers, apoc.any.properties(subPath).cost AS subPathCost
MATCH p = (subPathStartNode)-[route:CONNECTED_TO]-(subPathEndNode) WHERE (route.type IN ['TYPE1'] AND route.availableChannels > 0 ) AND route.networkId IN ['NETWORK1'] AND route.status IN ['STATUS1'] AND NOT(route.trailName IN ['TRAILNAME1'])
WITH , apoc.coll.indexOf(uniqueRouteNumbers, {noOfLinks: noOfLinks, totalCost: totalCost}) + 1 AS routeNumber
WITH index, routeNumber, sourceNode, targetNode, totalCost, costs, path, nodeIds, startNode, endNode, startLocation, endLocation, clliInRoutes, tidsInRoutes, noOfLinks, subPathStartNode, subPathEndNode, subPathCost, p, 10^2 AS factor
UNWIND relationships(p) AS routes
RETURN index+1 as routeNumbers, routeNumber, noOfLinks, totalCost as algoTotalCost, round(factortotalCost)/factor as totalCost, costs, subPathCost, routes.mileage as mileage, round(factor*routes.cost)/factor as cost, tidsInRoutes, clliInRoutes, startLocation as routeASiteName, endLocation as routeZSiteName, routes.aSiteName as linkASiteName, routes.zSiteName as linkZSiteName, CASE WHEN startNode IS NULL AND startLocation=routes.zSiteName THEN routes.zEquipmentTid WHEN startNode IS NULL AND startLocation=routes.aSiteName THEN routes.aEquipmentTid ELSE startNode END as routeAEquipmentTid, CASE WHEN endNode IS NULL AND endLocation=routes.zSiteName THEN routes.zEquipmentTid WHEN endNode IS NULL AND endLocation=routes.aSiteName THEN routes.aEquipmentTid ELSE endNode END as routeZEquipmentTid, routes.aEquipmentTid as linkAEquipTid, routes.zEquipmentTid as linkZEquipTid, routes.trailId as linkTrailId, routes.trailName as linkName, CASE WHEN routes.trailNameAlias = '-' OR routes.trailNameAlias IS NULL THEN routes.trailName ELSE routes.trailNameAlias END as linkTrailNameAlias, routes.networkId as linkNetworkId, routes.subNetworkId as linkSubNetworkId, routes.totalChannels as linkTotalChannels, routes.totalChannelsUsed as linkTotalChannelsUsed, routes.capacityUtilization as capacityUtilization, routes.linkColorByUtilization as linkColorByUtilization, routes.pathId as linkPathId, routes.latency as latency, routes.rtd as rtd, routes.type as linkType, routes.aSiteId as linkASiteId, routes.zSiteId as linkZSiteId, routes.aSiteStatus as linkASiteStatus, routes.zSiteStatus as linkZSiteStatus, routes.aSiteLatitude as aSiteLatitude, routes.aSiteLongitude as aSiteLongitude, routes.zSiteLatitude as zSiteLatitude, routes.zSiteLongitude as zSiteLongitude, routes.aSiteLata as linkASiteLata, routes.zSiteLata as linkZSiteLata, routes.aSiteScisCode as linkASiteScisCode, routes.zSiteScisCode as linkZSiteScisCode, routes.aSiteMeccaCode as linkASiteMeccaCode, routes.zSiteMeccaCode as linkZSiteMeccaCode, routes.aSiteRegion as linkASiteRegion, routes.zSiteRegion as linkZSiteRegion, routes.aSiteMarket as linkASiteMarket, routes.zSiteMarket as linkZSiteMarket, routes.aSiteAddress as linkASiteAddress, routes.zSiteAddress as linkZSiteAddress, routes.aSiteCity as linkASiteCity, routes.zSiteCity as linkZSiteCity, routes.aSiteState as linkASiteState, routes.zSiteState as linkZSiteState, routes.aSiteZip as linkASiteZip, routes.zSiteZip as linkZSiteZip, routes.aSiteCountry as linkASiteCountry, routes.zSiteCountry as linkZSiteCountry, routes.aEquipmentId as linkAEquipmentId, routes.zEquipmentId as linkZEquipmentId, routes.aEquipmentName as linkAEquipmentName, routes.zEquipmentName as linkZEquipmentName, routes.aEquipmentType as linkAEquipmentType, routes.zEquipmentType as linkZEquipmentType, routes.aFunctionalType as linkAFunctionalType, routes.zFunctionalType as linkZFunctionalType, routes.aEquipmentModel as linkAEquipmentModel, routes.zEquipmentModel as linkZEquipmentModel, routes.aEquipmentVendor as linkAEquipmentVendor, routes.zEquipmentVendor as linkZEquipmentVendor, routes.aRelayRack as linkARelayRack, routes.zRelayRack as linkZRelayRack, routes.aEquipmentPhyShelfNo as linkAEquipmentPhyShelfNo, routes.zEquipmentPhyShelfNo as linkZEquipmentPhyShelfNo, routes.aEquipmentLogShelfNo as linkAEquipmentLogShelfNo, routes.zEquipmentLogShelfNo as linkZEquipmentLogShelfNo, routes.aEquipmentPhysicalTid as linkAEquipmentPhysicalTid, routes.zEquipmentPhysicalTid as linkZEquipmentPhysicalTid, routes.aPortAid as linkAPortAid, routes.zPortAid as linkZPortAid ORDER BY CASE WHEN totalCost <=0 THEN noOfLinks ELSE totalCost END, routeNumbers ASC

2

ok can someone tell me how do I return a new column where the values start from 1. The logic is to keep it 1 as long as the combination of 2 other fields is unique and as soon as the uniqueness changes increment the new column value by 1.

3

The way the query is written, it will call the shortest path method for each pair of ‘a’ and ‘z’ nodes. Each pair can return up to 5 paths.

You have two match statements back to back, which will create a Cartesian product of the results from each. This will creates pairs of ‘a’ and ‘z’ values if both are not singletons. I suspect this is a cause of you getting more than 5 rows back.

To answer your question, cypher is not a scripting language so it does have ability to create state in a variable. Things flow top to bottom. What I have done as a work around when it is necessary is to collect all the data then use the reduce function to loop through the data and maintain what ever state and result I need in a map that gets updated during each iteration, or added to a list. The result of the reduce has my results. This is an atypical use case. I suspect in your case you should look at refactoring your query to eliminate the (a,z) pairs you don’t want to have their shortest paths determined.

4

Yep, that's what I observed and trying to limit the a and z combinations so that it can retrieve the limited shortestpath results. Thank you for pointing that out. So I tried limiting the a and z combinations by using limit but it wasn't returning the shortest paths first but the longest ones first so I'm wondering if there's a way to get the shortest ones first. I'm checking if there's some default ordering right before the call to shortestpath algorithm. Do you know if there's any? or how to achieve that?

I've tried something like below limiting the a and z combinations but it's not giving the shortest paths first something that would be at the end of the list of shortestpaths.

... WITH DISTINCT z, a, g limit 5 CALL gds.shortestPath.yens.stream( 'rakesh', { sourceNode: a, targetNode: z, k:5, relationshipWeightProperty: 'mileage' }) ...