Filtering result by relationship property

[vramkri]

Hi There,

I am a beginner and I am trying to write a query to filter my results using the date property of my relationship. But it doesn't seem to work.

CREATE (U1:Person {UID:1,Name:'Person1'}),
(U2:Person {UID:2,PName:'Person2'}),
(U3:Person {UID:3,PName:'Person3'}),
(U4:Person {UID:4,PName:'Person4'}),
(U5:Person {UID:5,PName:'Person5'}),
(U6:Person {UID:6,PName:'Person6'}),
(U7:Person {UID:7,PName:'Person7'}),
(U8:Person {UID:8,PName:'Person8'}),
(U9:Person {UID:9,PName:'Person9'}),
(U10:Person {UID:10,PName:'Person10'})

MATCH (P1:Person {UID:1}), (P2:Person {UID:2}), (P3:Person {UID:3})
CREATE (P1)-[:Met {date:datetime("2020-04-25T14:00")}]->(P2),
(P1)-[:Met {date:datetime("2020-04-25T14:00")}]->(P3)

MATCH (P2:Person {UID:2}), (P4:Person {UID:4}), (P5:Person {UID:5})
CREATE (P2)-[:Met {Date:datetime('2020-04-26T10:00')}]->(P4),
(P2)-[:Met {Date:datetime('2020-04-26T10:00')}]->(P5)

MATCH (P3:Person {UID:3}),(P6:Person {UID:6}), (P7:Person {UID:7})
CREATE (P3)-[:Met {Date:datetime('2020-04-26T14:00')}]->(P6),
(P3)-[:Met {Date:datetime('2020-04-26T14:00')}]->(P7)

MATCH (P4:Person {UID:4}), (P8:Person {UID:8}), (P9:Person {UID:9})
CREATE (P4)-[:Met {Date:datetime('2020-04-27T8:00')}]->(P8),
(P4)-[:Met {Date:datetime('2020-04-27T8:00')}]->(P9)

MATCH (P8:Person {UID:8}),(P10:Person {UID:10})
CREATE (P8)-[:Met {Date:datetime('2020-04-27T10:00')}]->(P10)

MATCH (P2:Person {UID:2}),(P4:Person {UID:4})
CREATE (P2)-[:Met {Date:datetime('2020-04-28T14:00')}]->(P11:Person {UID:11,PName:'Person11'}),
(P2)-[:Met {Date:datetime('2020-04-28T14:00')}]->(P4)

Match Query
Match Query 1:

MATCH p = (a:Person {UID:2})-[rels:Met*]-(b:Person) WHERE All(r IN rels WHERE datetime(r.date) < datetime("2020-04-30T00:00Z")) RETURN a.UID AS PersonID, b.UID AS FriendID,p , length(p) AS depth Order by depth

This returns only 2 rows, while all dates in my relationship are below 2020-04-30.

Match Query 2:

MATCH p = (a:Person {UID:2})-[rels:Met*]-(b:Person) WHERE All(r IN rels WHERE datetime(r.date) < datetime("2020-04-28T00:00Z")) RETURN a.UID AS PersonID, b.UID AS FriendID,p , length(p) AS depth Order by depth

This also returns only 2 rows, which is ok. But something seems to not work.

Match Query 3:
MATCH p = (a:Person {UID:2})-[rels:Met*]-(b:Person) RETURN a.UID AS PersonID, b.UID AS FriendID,p , length(p) AS depth Order by depth

returns all rows

Kindly help. Many thanks in advance...

[rushikesh]

Hey @vramkri , the property names are case sensitive. Here date and Date are two different things, that's why you are not getting a required result.

[vramkri]

Hi rushikesh,

Thanks for your help. After making the changes as per your suggestion, the query picks the Date, but still the results are incorrect.

Foe e.g the query shouldn't have picked UID 11, but it does

MATCH p = (a:Person {UID:2})-[rels:Met*]-(b:Person) WHERE (a.UID<>b.UID) AND ALL(idx in range(0, size(rels)-2) WHERE (rels[idx]).Date < datetime("2020-04-27T00:00Z") AND (rels[idx]).Date < (rels[idx+1]).Date) RETURN DISTINCT a.UID AS PersonID, b.UID AS FriendID,p,size(rels), length(p) AS depth Order by FriendID, depth

And the following query should have returned UIDs 1 and 3 alone, but picks 1,4,5 and 11 and doesn't pick 3.

MATCH p = (a:Person {UID:2})-[rels:Met*]-(b:Person) WHERE (a.UID<>b.UID) AND ALL(idx in range(0, size(rels)-2) WHERE (rels[idx]).Date < datetime("2020-04-26T00:00Z") AND (rels[idx]).Date < (rels[idx+1]).Date) RETURN DISTINCT a.UID AS PersonID, b.UID AS FriendID,p,size(rels), length(p) AS depth Order by FriendID, depth

Thanks a lot

--Ramki

[rushikesh]

vramkri:

CREATE (P1)-[:Met {date:datetime("2020-04-25T14:00")}]->(P2),

Here, did you change date by Date?

[vramkri]

My bad... I didn't. Will try and revert. Thanks for your quick response.

[vramkri]

Hi rushikesh,

Changed "CREATE (P1)-[:Met {date:datetime("2020-04-25T14:00")}]->(P2)," to "CREATE (P1)-[:Met {Date:datetime("2020-04-25T14:00")}]->(P2)," and tried. Still the same result.

[rushikesh]

Hey @vramkri,

vramkri:

ALL(idx in range(0, size(rels)-2) WHERE (rels[idx]).Date < datetime("2020-04-27T00:00Z") AND (rels[idx]).Date < (rels[idx+1]).Date)

this always gives you true, because the value of size(rels)-2 is -1 or 0, which leads to null value in the where condition.
like this: all (x in range(0,-1) where null)

[vramkri]

Good Day rushikesh...

If that is the case, I shouldn't get UID:9 and U:10 as well.

I think there are 2 issues here:
1.The cyclic reference between UID:4 and UID:8, though different dates
2. Date comparison

Why I say date comparison is an issue is because, after removing the cyclic relationship when I run the query below:

MATCH p = (a:Person {UID:2})-[rels:Met*]-(b:Person) WHERE (a.UID<>b.UID) AND ALL(idx in range(0, size(rels)-2) WHERE (rels[idx]).Date < datetime("2020-04-30T00:00Z")) RETURN DISTINCT a.UID AS PersonID, b.UID AS FriendID,p,size(rels), length(p) AS depth Order by depth

It returns all nodes, which i actually correct. But when I use the one below:

MATCH p = (a:Person {UID:2})-[rels:Met*]-(b:Person) WHERE (a.UID<>b.UID) AND ALL(idx in range(0, size(rels)-2) WHERE (rels[idx]).Date < datetime("2020-04-27T00:00Z")) RETURN DISTINCT a.UID AS PersonID, b.UID AS FriendID,p,size(rels), length(p) AS depth Order by depth

This returns UID:11, whose Date value is "2020-04-28T14:00".

Or maybe my graph node creation itself is wrong...

[vramkri · Accepted solution]

Hi rushikesh,

I somehow managed to make it to work after a little compromise. I removed the time pert from the property. May be that was not needed too.

And I changed the query to use relationship instead of range. It was easy to understand too... Thanks a lot for your support.

Happy Coding...