带有多个过滤器的Neo4J查询未返回预期结果 [英] Neo4J Query with multiple filter does not return the expected result

问题描述

我正在使用以下Neo4JClient代码查询一星期至21天且大于21天的所有在一周内到期的发货计数.

    var query = GraphClient.Cypher
        .Merge("(user:Person{InternalId:{userId}})")
        .WithParam("userId", userId)
        .With("user")
        .Match("(veh:Vehicle)<-[:HAS_VEHICLE_TYPE]-(load:ActiveShipment)-[:SHIPPED_BY]->(shipper:Shipper), (user)-[:WORKS_FOR_COMPANY]->(transporter:Transporter)")
        .Where("((load.RestrictedBidding = false) OR (user)-[:WORKS_FOR_COMPANY]->(transporter)<-[:HAS_TRANSPORTER]-(shipper)<-[:SHIPPED_BY]-(load))")
        .AndWhere("(load)-[:HAS_VEHICLE_TYPE]->(veh)<-[:HAS_VEHICLE]-(transporter)")
        .With("load, transporter, shipper, user")
        .Match("p=(shipFrom:City)<-[:SHIP_FROM_CITY]-(load)-[:SHIP_TO_CITY]->(shipTo:City)")
        .With("load,  collect(shipFrom) as FromCities, collect(shipTo) as ToCities, COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate <= {withinWeek})) as LessThanWeek , COUNT(filter(x IN Nodes(p) WHERE x:Shipment and (x.PickupDate > {withinWeek}) and (x.PickupDate <= {within3Week}))) as NextWeekToFortnight, COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate > {within3Week})) as LaterThan3Week")
        .WithParams(new { withinWeek = next7Days, within3Week = next3Weeks })
        .Return((load,FromCities, ToCities, LessThanWeek , NextWeekToFortnight, LaterThan3Week )=>new 
        { 
            OneWeek = Return.As<long>("LessThanWeek"),
            SevenToTwentyOneDays = Return.As<long>("NextWeekToFortnight"),
            Later = Return.As<long>("LaterThan3Week")

        });

这会生成以下Cypher查询

MERGE (user:Person{InternalId:2})   
WITH user   
MATCH (veh:Vehicle)<-[:HAS_VEHICLE_TYPE]-(load:ActiveShipment)-[:SHIPPED_BY]->(shipper:Shipper), 
      (user)-[:WORKS_FOR_COMPANY]->(transporter:Transporter)   WHERE ((load.RestrictedBidding = false) OR (user)-[:WORKS_FOR_COMPANY]->(transporter)<-[:HAS_TRANSPORTER]-(shipper)<-[:SHIPPED_BY]-(load))   AND (load)-[:HAS_VEHICLE_TYPE]->(veh)<-[:HAS_VEHICLE]-(transporter)   
WITH load, transporter, shipper, user   
MATCH p=(shipFrom:City)<-[:SHIP_FROM_CITY]-(load)-[:SHIP_TO_CITY]->(shipTo:City)   
WITH load,  collect(shipFrom) as FromCities, collect(shipTo) as ToCities, 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate <= 4/30/2014 12:00:00 AM +05:30)) as LessThanWeek , 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and (x.PickupDate > 4/30/2014 12:00:00 AM +05:30) and (x.PickupDate <= 5/14/2014 12:00:00 AM +05:30))) as NextWeekToFortnight, 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate > 5/14/2014 12:00:00 AM +05:30)) as LaterThan3Week   
RETURN LessThanWeek AS OneWeek , NextWeekToFortnight AS SevenToTwentyOneDays , LaterThan3Week AS Later 

但是当我得到OneWeek，SevenToTwentyOneDays&时结果不是我期望的后来都是1.

要弄清楚我在做什么:我想做的是首先根据我的选择标准获取所有负载，然后要根据它们在交货日期的位置来获取这些负载的计数，并仅返回计数.因此，此查询中确实需要WITH.

Q1:这甚至是写的有效查询吗?以及如何解决.

Q2:像上面那样使用过滤器会影响我的查询性能吗?如果是，是否有更简单的方法来做到这一点?

顺便说一句，上面粘贴的Cypher查询来自neo4jClient的调试文本"，这意味着实际的查询已参数化，但调试文本正在写出参数值以便于理解，因此日期无法正确编写.

问候 基兰

解决方案

最后发现我做错了.

这部分代码不正确.

WITH load,  collect(shipFrom) as FromCities, collect(shipTo) as ToCities, 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate <= 4/30/2014 12:00:00 AM +05:30)) as LessThanWeek , 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and (x.PickupDate > 4/30/2014 12:00:00 AM +05:30) and (x.PickupDate <= 5/14/2014 12:00:00 AM +05:30))) as NextWeekToFortnight, 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate > 5/14/2014 12:00:00 AM +05:30)) as LaterThan3Week   
RETURN LessThanWeek AS OneWeek , NextWeekToFortnight AS SevenToTwentyOneDays , LaterThan3Week AS Later 

相反，我需要的是这样的东西:

WITH load,  collect(shipFrom) as FromCities, collect(shipTo) as ToCities, 
     filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate <= 4/30/2014 12:00:00 AM +05:30) as LessThanWeek , 
     filter(x IN Nodes(p) WHERE x:Shipment and (x.PickupDate > 4/30/2014 12:00:00 AM +05:30) and (x.PickupDate <= 5/14/2014 12:00:00 AM +05:30)) as NextWeekToFortnight, 
     filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate > 5/14/2014 12:00:00 AM +05:30) as LaterThan3Week   
RETURN 
   COUNT( DISTINCT LessThanWeek) AS OneWeek , 
   COUNT( DISTINCT NextWeekToFortnight) AS SevenToTwentyOneDays , 
   COUNT( DISTINCT LaterThan3Week) AS Later 

I am using the following Neo4JClient code to query for all the shipment count that is due within a wee, one week to 21 days and greater than 21 days.

    var query = GraphClient.Cypher
        .Merge("(user:Person{InternalId:{userId}})")
        .WithParam("userId", userId)
        .With("user")
        .Match("(veh:Vehicle)<-[:HAS_VEHICLE_TYPE]-(load:ActiveShipment)-[:SHIPPED_BY]->(shipper:Shipper), (user)-[:WORKS_FOR_COMPANY]->(transporter:Transporter)")
        .Where("((load.RestrictedBidding = false) OR (user)-[:WORKS_FOR_COMPANY]->(transporter)<-[:HAS_TRANSPORTER]-(shipper)<-[:SHIPPED_BY]-(load))")
        .AndWhere("(load)-[:HAS_VEHICLE_TYPE]->(veh)<-[:HAS_VEHICLE]-(transporter)")
        .With("load, transporter, shipper, user")
        .Match("p=(shipFrom:City)<-[:SHIP_FROM_CITY]-(load)-[:SHIP_TO_CITY]->(shipTo:City)")
        .With("load,  collect(shipFrom) as FromCities, collect(shipTo) as ToCities, COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate <= {withinWeek})) as LessThanWeek , COUNT(filter(x IN Nodes(p) WHERE x:Shipment and (x.PickupDate > {withinWeek}) and (x.PickupDate <= {within3Week}))) as NextWeekToFortnight, COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate > {within3Week})) as LaterThan3Week")
        .WithParams(new { withinWeek = next7Days, within3Week = next3Weeks })
        .Return((load,FromCities, ToCities, LessThanWeek , NextWeekToFortnight, LaterThan3Week )=>new 
        { 
            OneWeek = Return.As<long>("LessThanWeek"),
            SevenToTwentyOneDays = Return.As<long>("NextWeekToFortnight"),
            Later = Return.As<long>("LaterThan3Week")

        });

this generates the following Cypher query

MERGE (user:Person{InternalId:2})   
WITH user   
MATCH (veh:Vehicle)<-[:HAS_VEHICLE_TYPE]-(load:ActiveShipment)-[:SHIPPED_BY]->(shipper:Shipper), 
      (user)-[:WORKS_FOR_COMPANY]->(transporter:Transporter)   WHERE ((load.RestrictedBidding = false) OR (user)-[:WORKS_FOR_COMPANY]->(transporter)<-[:HAS_TRANSPORTER]-(shipper)<-[:SHIPPED_BY]-(load))   AND (load)-[:HAS_VEHICLE_TYPE]->(veh)<-[:HAS_VEHICLE]-(transporter)   
WITH load, transporter, shipper, user   
MATCH p=(shipFrom:City)<-[:SHIP_FROM_CITY]-(load)-[:SHIP_TO_CITY]->(shipTo:City)   
WITH load,  collect(shipFrom) as FromCities, collect(shipTo) as ToCities, 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate <= 4/30/2014 12:00:00 AM +05:30)) as LessThanWeek , 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and (x.PickupDate > 4/30/2014 12:00:00 AM +05:30) and (x.PickupDate <= 5/14/2014 12:00:00 AM +05:30))) as NextWeekToFortnight, 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate > 5/14/2014 12:00:00 AM +05:30)) as LaterThan3Week   
RETURN LessThanWeek AS OneWeek , NextWeekToFortnight AS SevenToTwentyOneDays , LaterThan3Week AS Later 

but the results are not what I expect as I am getting OneWeek , SevenToTwentyOneDays & Later all as 1.

To clarify what I am doing: I am trying to do is first get all the loads based on my selection criteria, and then want to get the counts of these loads based where they stand on the delivery dates and only return the count. SO the WITH is really needed in this query.

Q1: Is this even a valid query to write? and how can I fix it.

Q2: Will the use of filter like above impact my query performance and if yes Is there a simpler way to do this?

Edit: BTW the Cypher query pasted above is from the Debug Text of neo4jClient which means the actual query is parameterized but the debug text is writing out the parameter values for easy understanding so the dates are not written properly.

Regards Kiran

解决方案

Finally found what I was doing wrong.

This part of my code was incorrect.

WITH load,  collect(shipFrom) as FromCities, collect(shipTo) as ToCities, 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate <= 4/30/2014 12:00:00 AM +05:30)) as LessThanWeek , 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and (x.PickupDate > 4/30/2014 12:00:00 AM +05:30) and (x.PickupDate <= 5/14/2014 12:00:00 AM +05:30))) as NextWeekToFortnight, 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate > 5/14/2014 12:00:00 AM +05:30)) as LaterThan3Week   
RETURN LessThanWeek AS OneWeek , NextWeekToFortnight AS SevenToTwentyOneDays , LaterThan3Week AS Later 

Instead what I needed was something like this:

WITH load,  collect(shipFrom) as FromCities, collect(shipTo) as ToCities, 
     filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate <= 4/30/2014 12:00:00 AM +05:30) as LessThanWeek , 
     filter(x IN Nodes(p) WHERE x:Shipment and (x.PickupDate > 4/30/2014 12:00:00 AM +05:30) and (x.PickupDate <= 5/14/2014 12:00:00 AM +05:30)) as NextWeekToFortnight, 
     filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate > 5/14/2014 12:00:00 AM +05:30) as LaterThan3Week   
RETURN 
   COUNT( DISTINCT LessThanWeek) AS OneWeek , 
   COUNT( DISTINCT NextWeekToFortnight) AS SevenToTwentyOneDays , 
   COUNT( DISTINCT LaterThan3Week) AS Later 

这篇关于带有多个过滤器的Neo4J查询未返回预期结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！