Neo4j Cypher query: order collection, take first n elements

4
votes

I'm having trouble writing a Cypher query for this social networking type of application. It involves users that add posts (essentially an image with a description), which users can review.

In Cypher the graph model is this: (user)-[:WROTE_REVIEW]->(review)-[:EVALUATES]->(post)

The query I'm trying to write should return all the posts that a particular user has reviewed, along with some general information about the reviews of a post.

This includes:

  1. (the post's ID)
  2. the post's image
  3. the post's description
  4. the total number of reviews of this post
  5. the total number of reviewers of this post
  6. the avatars of the last 6 reviewers of this post, ordered by the date of the review, descending

I think I've managed to complete the first five items, but item 6 is giving me trouble. The query below gives me all of the avatars, while I only need the last 6.

START user=node(2515)
MATCH (user)-[:WROTE_REVIEW]->()-[:EVALUATES]->(post)
WITH distinct post
MATCH (review)-[:EVALUATES]->(post)
WITH post, count(review) as reviews
MATCH (reviewer)-[:WROTE_REVIEW]->()-[:EVALUATES]->(post)
WITH post, reviews, count(distinct reviewer) as reviewers, collect(distinct reviewer.Avatar) as avatars
ORDER BY post.CreationTime DESC
RETURN post.Id, post.Image, post.Description, reviews, reviewers, avatars;

Can someone show me how to order the avatars by review date (i.e. review.CreationTime) descending, and take the first six items?

Thanks!

2
neo4jcypher

2 Answers

4
votes

Instead of sorting the collection, you might sort the rows first and take the first 6 of the collection of the reviewers. so change the last match-with to something like this, 

MATCH (reviewer)-[:WROTE_REVIEW]->(review)-[:EVALUATES]->(post)
With distinct post, reviewer, review
ORDER BY post.CreationTime DESC, review.CreationTime DESC
Return post, count(reviewer) as reviewers, collect(reviewer.Avatar)[0..5] as avatars

The access to the collection with index such as [0..5] requires the verion of 2.0 M5.

3
votes

The collection slices in Neo4j 2.0 were the key. I had to put in some distinct clauses in my results since some duplicates were coming up. This is what I ended up with:

START user=node(2515)
MATCH (user)-[:WROTE_REVIEW]->()-[:EVALUATES]->(post)
WITH distinct post
MATCH (review)-[:EVALUATES]->(post)
WITH post, count(review) as reviews
MATCH (reviewer)-[:WROTE_REVIEW]->(review)-[:EVALUATES]->(post)
WITH distinct post, reviewer, review, reviews
ORDER BY review.CreationTime, post.CreationTime DESC
RETURN post.Id AS postId, post.Image AS postImage, post.Description AS postDescription, reviews AS Reviews, count(distinct reviewer) AS Reviewers, collect(distinct reviewer.Avatar)[0..5] AS Avatars