Get excellent values based on two neo4j nodes
There are many "custom" nodes, each user can send money to another user.
(:User)-[r:SENT_MONEY]->(:User)
here r has the properties
- created_at = timestamp ()
- money_transferred = the amount of money transferred
How can I find one last money sent by a user to another user or the last money received from another user in one request.
I tried this query
MATCH (from:User)-[r:SENT_MONEY]->(to:User)
where (id(from)=1234 OR id(to)=1234)
return max(r.created_at) as sent_at,
r.money_transferred as amount, from.username,to.username
order by last_amount_sent_at DESC
The results look like this:
sent_at | amount | from.username | to.username
1408961056 | 20 | user1 | user2
1408961041 | 30 | user2 | user1
1408961028 | 50 | user1 | user3
1408951163 | 20 | user4 | user1
1408951140 | 10 | user1 | user4
On this request, the user "user1" records with "user2" and "user4" is received twice. It should be the only one with the last transaction between users like
sent_at | amount | from.username | to.username
1408961056 | 20 | user1 | user2
1408961028 | 50 | user1 | user3
1408951163 | 20 | user4 | user1
source to share
Satish,
Here's a query that I think will do what you need.
MATCH (m:User {username : 'user1'})-[r:SENT_MONEY]-(n:User)
WITH m, n, collect(r) AS rs, max(r.created_at) AS p
WITH m, n, filter(x IN rs WHERE x.created_at = p) AS l
RETURN STARTNODE(l[0]), ENDNODE(l[0]), l[0]
For each pair of users, you collect transactions and find the last one, then return the start and end node of that last transaction.
Grace and peace
Jim
In response to your further question, you can modify a query like this (for example) to avoid a collection or element error:
MATCH (m:User {username : 'user1'})-[r:SENT_MONEY]-(n:User)
WITH m, n, collect(r) AS rs, max(r.created_at) AS p
WITH m, n, filter(x IN rs WHERE x.created_at = p) AS o
RETURN m, n, o[0], (o[0]).money_transferred
source to share