Neo4J Query with multiple filter does not return the expected result

Question

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

cybersam cybersam · Accepted Answer · 2014-04-22T20:24:01

Q1: The date/time strings (e.g., "4/30/2014 12:00:00 AM +05:30") are not supported by Cypher. I suggest using epoch time (in milliseconds) -- presumably your nodes' date/time properties are using that?

Q2: This is a somewhat simpler (and probably faster) query. You had a lot of duplicated patterns, which have been removed. I was able to come up with a consolidated MATCH pattern. Also, I am proposing the use of parameters id, date1 and date2, which will allow the neo4j engine to cache the execution plan and re-use it with every query:

MERGE (user:Person{InternalId:{id}})   
WITH user   
MATCH (user)-[:WORKS_FOR_COMPANY]->(transporter:Transporter)-[:HAS_VEHICLE]->(veh:Vehicle)<-[:HAS_VEHICLE_TYPE]-(load:ActiveShipment)-[:SHIPPED_BY]->(shipper:Shipper)
WHERE ((load.RestrictedBidding = false) OR (transporter)<-[:HAS_TRANSPORTER]-(shipper)<-[:SHIPPED_BY]-(load))   
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 <= {date1})) as LessThanWeek , 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and (x.PickupDate > {date1}) and (x.PickupDate <= {date2}))) as NextWeekToFortnight, 
     COUNT(filter(x IN Nodes(p) WHERE x:Shipment and x.PickupDate > {date2})) as LaterThan3Week   
RETURN LessThanWeek AS OneWeek , NextWeekToFortnight AS SevenToTwentyOneDays , LaterThan3Week AS Later;

Neo4J Query with multiple filter does not return the expected result

3 Answers