CODEWITHSUNDEEP
neo4jcypher
user2819872
user2819872

Reputation: 73

Neo4j Cypher Query to aggregate count of connecting nodes

I have an email graph model located here: http://console.neo4j.org/r/go164n

I am trying to find the count of messages being sent from one email address to another and vice versa.

Basically, there are several email address nodes along with message nodes that connect the email addresses. The relationships between these message and email address nodes are :SENT or :Received_By

They can only go one way, so the addresses would be in these formats

For each message node, there can only be one sender, but multiple receivers are possible.

What I am trying to get is an aggregate of message counts that are going between two specific email addresses.

That means in my example, I am trying to get an aggregate of:

MATCH (n:EmailAddress)-->(m:Message)-->(o:EmailAddress)WHERE n.address='[email protected]' AND o.address='[email protected]'WITH n, count(DISTINCT m) AS messageCount, oRETURN n.address, messageCount, o.address

AND

MATCH (n:EmailAddress)<--(m:Message)<--(o:EmailAddress)WHERE n.address='[email protected]' AND o.address='[email protected]'WITH n, count(DISTINCT m) AS messageCount, oRETURN n.address, messageCount, o.address

Instead of getting two separate counts of the messages going one way or the other, I was wondering if there was a cypher query to automatically sum these counts up

The reason why MATCH (n:EmailAddress)--(m:Message)--(o:EmailAddress) won't work is because there may be a third email address node which could potentially send a message to the other two email addresses. This would make both email addresses receivers and I do not want to count these messages.

Thank you!

EDIT:

Hey! Actually, I may have confused you. I want the return to be an aggregate of the messages between the two emails. Something that will end up with

n.address messageCount o.address
[email protected] 2 [email protected]

I can't use MATCH (n:EmailAddress)--(m:Message)--(o:EmailAddress) because it will also count emails that were sent to both of them but from neither reciever

Upvotes: 4

Views: 1610

Answers (1)

mttdbrd
mttdbrd

Reputation: 1831

I think this does what you want:

MATCH (n:EmailAddress)-[:SENT]-(m:Message)-[:Received_By]-(o:EmailAddress)
WHERE (n.address='[email protected]' AND o.address='[email protected]') 
OR (o.address='[email protected]' AND n.address='[email protected]')
WITH n, m, count(DISTINCT m) AS messageCount, o
RETURN n.address, messageCount, o.address, m.body

There seems to be an error in your create code too because it creates two separate graphs with the same data. You need parens around the identifiers:

CREATE (_6:EmailAddress { address:"[email protected]" }),
(_7:EmailAddress { address:"[email protected]" }),
(_8:EmailAddress { address:"[email protected]" }),
(_9:Message { body:"Sam Sucks" }),
(_10:Message { body:"I suck" }),
(_11:Message { body:"We ALL suck" }),
(_6)-[:SENT]->(_9), 
(_7)-[:SENT]->(_10), 
(_8)-[:SENT]->(_11),
(_9)-[:Received_By]->(_7), 
(_10)-[:Received_By]->(_6), 
(_11)-[:Received_By]->(_6), 
(_11)-[:Received_By]->(_7)

It returns the following:

n.address           messageCount    o.address               m.body
[email protected]   1               [email protected]      Sam Sucks
[email protected]  1               [email protected]       I suck

Query took 41 ms and returned 2 rows.

Edit 1:

If all you want is a count, that simplifies the query:

MATCH (n:EmailAddress)-[:SENT]-(m:Message)-[:Received_By]-(o:EmailAddress)
WHERE (n.address='[email protected]' AND o.address='[email protected]') 
OR (o.address='[email protected]' AND n.address='[email protected]')
RETURN count(*)

Upvotes: 2

Related Questions