CODEWITHSUNDEEP
neo4jcypher
Moritz Kampfinger
Moritz Kampfinger

Reputation: 154

Count how many dates in a list are between two dates

I have the given schema

(t1:Tag)-[c:CONNECTED_TO]-(t2:Tag)

Each relationship of type :CONNECTED_TO has a property that is storing dates as a list.

{
  "dates": [
    "2019-11-27",
    "2019-01-24",
    "2017-06-27"
  ]
}

Now I want to find each relationship where one of the dates is between two dates.

MATCH ()-[c:CONNECTED_TO]-()
WHERE ANY (item IN c.dates WHERE date({year: 2019, month: 12}) > item >= date({year: 2019, month: 10}))
RETURN c

But in addition, I also want to count how many dates in the list-property are between those two dates. For the above example, it should return 1 because only one date on the list is between 2019-12-01 and 2019-10-01.

How can I do that?

Edit

I found the following solution for my problem:

MATCH ()-[c:CONNECTED_TO]-()
WITH [x IN range(0,size(c.dates)-1) WHERE date({year: 2019, month: 12}) > c.dates[x] >= date({year: 2019, month: 1})] AS index, id(c) AS id
RETURN id, size(index) AS Size

Sadly this solution is very slow. The date-list can be of size 10.000 or higher. If there is somebody with a faster solution I would be happy to know.

Upvotes: 0

Views: 48

Answers (1)

cybersam
cybersam

Reputation: 66999

  1. Your MATCH is scanning ALL the nodes in your database to find the CONNECTED_TO relationships. You should be qualifying the end nodes with the Tag label to scan just the Tag nodes.
  2. You should be calculating the static date values just once.
  3. You should use the REDUCE function instead of list comprehension (which generates new lists that your query will just drop) to count the dates within range.
  4. There is no need to use the RANGE function, which also generates a list.

This query should be faster:

WITH date({year: 2019, month: 12}) AS a, date({year: 2019, month: 1}) AS b
MATCH (:Tag)-[c:CONNECTED_TO]-(:Tag)
RETURN ID(c) AS id, REDUCE(s=0, d IN c.dates | CASE WHEN a > d >= b THEN s + 1 ELSE s END) AS Size

Upvotes: 2

Related Questions