How to count items and then simultaneously return the first item and the count?

Question

I have a Item node, which can be LIKED by a User node, and what I'm trying to achieve is:

• List all unique Item nodes that were liked by a User, sorted by the time of LIKE
• For each Item node listed above, also list the User node that most recently liked that Item node

This is the query I'm trying to use:

MATCH (i:Item)<-[like:LIKES]-(u:User) 
WITH i, like, u, COUNT(u) as u_count
ORDER BY like.created DESC LIMIT 1
RETURN i, like.created, u, u_count;

However, this query only lists the first Item node. Any suggestions?

Answer 1

In your query you are counting the number of users per item per created attribute. My expectation is that unless there are multiple simultaneous likes per item that the user count will always be one. Then you are limiting that aggregated result to a single row.

Here is one approach that I think satisfies what you are attempting to accomplish.

// match the users and likes
MATCH (i:Item)<-[like:LIKES]-(u:User) 

// order the item likes by most recent timestamp
WITH i, like.created AS created, u
ORDER BY created DESC

// collect the ordered likes and users per item
WITH i, collect(created) AS liked, collect(u.name) AS users

// return the item, the time is was last liked, the user that last liked and the total number of likes
RETURN i.name, liked[0], users[0], size(liked) AS likes

// order by the most recent like
ORDER BY liked[0] DESC