cancel
Showing results for 
Search instead for 
Did you mean: 

Head's Up! Site migration is underway. Pause, resolving how to handle anonymous content

Relation Type containing a Date - Optimization

maxime_blais
Node Link

Hey guys I have this model:

CREATE (myInfo:Info {hash:'some hash'})
CREATE (Keanu:User {name:'Keanu Reeves'})
CREATE (Keanu)- [:INFO_DETECTED_ON_2020_05_04] - >(myInfo);

I want to be able to retrieve relation based on the date in the relation type, I was told that (by listening to video the secret neo4j sauce) that this was optimal if I need to search for a specific date, or a range of date (between)

Here is the query I came up with, to get the last 30 days info that are in the database.
I find it a bit dirty and I am not sure it is performing fast
How can I make sure my query is fast, I have difficulity understanding the output of PROFILE on the query. any help appreciated

// Get the info from the last 30 days from the relation name - Needs to be fast
PROFILE MATCH (User)- [r] - (info:Info)
WHERE  (type(r)) STARTS WITH 'INFO_DETECTED' AND
datetime() - duration('P30D') < datetime({year:toInteger(substring(type(r), size(type(r))- 10, 4)), month:toInteger(substring(type(r), size(type(r))- 5, 2)), day:toInteger(substring(type(r), size(type(r))- 2, 2))})
RETURN info;
3 REPLIES 3

maxime_blais
Node Link

Some example to replicate

// some query example, to show what type of query we could be doing in Neo4j with good performance
CREATE (myInfo:Info {hash: 'hash1'})
CREATE (myInfo2:Info {hash: 'hash123'})
CREATE (Keanu:User {name: 'Keanu Reeves'})
CREATE (Max:User {name: 'Max Blais'})
CREATE (Max)-[:_2020_01_01]->(myInfo)
CREATE (Max)-[:_2020_01_03]->(myInfo2)
CREATE (Keanu)-[:_2020_04_04]->(myInfo)
;

// Get the Info from the last 365 days from the relation name
MATCH ()-[r]-(info:Info)
  WHERE  (type(r)) STARTS WITH '_2' AND
  datetime() - duration({days: 365}) < datetime({year:  toInteger(substring(type(r), size(type(r)) - 10, 4)),
                                                 month: toInteger(substring(type(r), size(type(r)) - 5, 2)),
                                                 day:   toInteger(substring(type(r), size(type(r)) - 2, 2))})
RETURN DISTINCT (info);

// Find all user that have the same info
MATCH
  (user:User)-[r]-(info:Info)
  WHERE  (type(r)) STARTS WITH '_2'
  AND (info.hash = 'hash1' OR info.hash = 'hash123')
RETURN user, collect(info.hash) AS valueFound

maxime_blais
Node Link

Question answered in Filtering by Relationship Type - Contains
can be closed

clem
Graph Steward

I guess I'm missing something...

Why don't you store the date as a property of the relationship? Then you can query based the relationship with a specific date property (or range.). You'll need to convert the date property to some sort of integer value so that it can be efficiently searched for. You'll almost certainly want to make an index of the date property too.