CODEWITHSUNDEEP
neo4jcypher
Gaurav Jain
Gaurav Jain

Reputation: 419

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

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

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?

Upvotes: 2

Views: 94

Answers (1)

Dave Bennett
Dave Bennett

Reputation: 11216

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

Upvotes: 2

Related Questions