Want to implement a timer for each record in neo4j database

Question

Update:

What I am looking for is some kind of auto trigger mechanism that will do the job as I desire.

Use case:

A ranking based on the scores of posts. The total score of a post is a).the score of this post plus b).the sum of score of all its comments. Both a and b are have initial score 10, and will decrease one point every 24 hours since creation until it becomes 0. When the total score of a post becomes 0, it is regarded as inactive thereafter, removed from the rank but remains in the database for other purpose. This inactive post will no longer active even users comment on it in the future.

I've got a problem implementing a timer for each record in neo4j database.

Assume we have 1 million records, they are either a post or a comment of a post. They both have 10 points when created, and will decrease one point every 24 hours since creation until it becomes 0 and is regarded as inactive (set off the timer).

My naive thought is that we create an attribute of score and update the attribute every 24 hours. But given the size of the data (is growing when users create posts and comments), the intensive database operations are really slow.

Is there any way we could use to implement a timer, or other methods that could meet the needs?

Thanks.

Answer 1

As @Evgen suggests, one way would be to just store creation time and calculate the score as you query.

For example:

Create a comment:

CREATE (c:Comment {creationTime: timestamp(), text: {some_text}})

The timestamp() Cypher function will evaluate to the current server time, using milliseconds since the epoch format.

Query for all active comments:

MATCH (c:Comment) WHERE c.creationTime < (timestamp() + 86400000)
RETURN c

Find all Comments that were created within 24 hours. Be sure to create an index on the creationTime property:

CREATE INDEX ON Comment(creationTime);

Calculate and return points for all active comments:

Let's say you want to find all active comments for a given post and return the comments, ordered by score.

MATCH (p:Post {name: "somepost"})<-[:IS_ABOUT]-(c:Comment) 
WHERE c.creationTime < (timestamp() + 86400000)
RETURN c, 10 - ((timestamp() - c.creationTime) / 3600000) AS points

Answer 2

My naive thought is that we create an attribute of score and update the attribute every 24 hours. But given the size of the data (is growing when users create posts and comments), the intensive database operations are really slow.

I think the solution here is to change label of Comment and Post after the first time you understood that it's no more active. Thus you won't have to run through all the comments and posts each time and you will have only relevant. The implementing might be different. You can still run queries like

    MATCH (c:Comment) Set c.points = c.points-1 with c 
    Match(c) where c.points = 0 remove c:Comment set c:InactiveComment

Or you can use creation time.

updated:

If you don't want to remove label Comment. Whenever you create new Post or Comment you can set to them labels :New:Comment, :New:Post. And after post , comment became inactive you remove label New. You will still have good performance as :New:Comment << :Comment thus you get benefits from querying only through relevant data whenever you need.