UNWIND statement in a Cypher Query

user_12544202 0
Viewed 144

I have these nodes:

  1. user{user_id}: users
  2. thread{thread_id, post_date} : posts
  3. tag_id{tag_id}: the tag of the post

And these relationships:

  1. (user) - [: FOLLOWED] -> (tag) // the user follows the tag
  2. (thread) - [: BELONG_TO] -> (tag) // the post belongs to tag
  3. (user) - [: READ] -> (thread) // user reads the post

Now I want to query for each tag that user follows 5 posts that user has not read. I wrote cypher as follows:

MATCH (u:User)-[:FOLLOWED]->(t:Tag)
WHERE u.id = 39792
WITH collect(t) as tLists
UNWIND tLists as t
MERGE (u:User)-[:FOLLOWED]->(t:Tag)<-[:BELONG_TO]-(th:Thread)<-[r:READ]-()
WHERE not (u)-[:READ]->(th)
RETURN u.id, th.id, t.id, duration.inDays(datetime(), datetime({ epochmillis: apoc.date.parse(th.post_date)})).days as time, count(r) as count_view
ORDER BY time DESC, count_view DESC
LIMIT 5

But it doesn't work. Help me

cypher neo4j
1 Answers
  1. Do not use MERGE if you do not intend to write to the DB. (And if you do want to write to the DB, you must learn how to use MERGE properly to avoid unwanted results.)
  2. Do not use aggregating functions (like COUNT) unless you have read the documentation and understand how to use them. In particular, you must understand what "grouping keys" are and how they affect the behavior of aggregating functions.
  3. You should learn how degreeness checks can efficiently count certain relationship patterns.

This query may work for you:

MATCH (u:User)-[:FOLLOWED]->(t:Tag)<-[:BELONG_TO]->(th)
WHERE u.id = 39792 AND NOT EXISTS((u)-[:READ]->(th))
RETURN u.id, th.id, t.id,
  duration.inDays(datetime(), datetime({ epochmillis: apoc.date.parse(th.post_date)})).days AS days,
  SIZE((th)<-[:READ]-()) as count_view
ORDER BY days DESC, count_view DESC
LIMIT 5

[UPDATE]

Here is one way to get the 5 newest threads with the most views, per tag followed by the user, that the user has not yet read:

MATCH (u:User)-[:FOLLOWED]->(t:Tag)<-[:BELONG_TO]->(th)
WHERE u.id = 39792 AND NOT EXISTS((u)-[:READ]->(th))
WITH t.id AS tagId, th.id AS threadId
ORDER BY apoc.date.parse(th.post_date) DESC, SIZE((th)<-[:READ]-()) DESC
RETURN tagId, COLLECT(threadId)[..5] AS threads
user_9747310
Related