1

I’m trying to run a cypher query on nodes with two way relationships and would like to count each of those relationships as well.

Example:

Nodes: store + customer 
Relationships: sold + bought

In an ideal scenario, I’d want to see both relationships connecting the store and customer. However, there are cases when only the customer is reporting they bought from the store, and vice versa when only the store is reporting they sold to the customer, but the customer hasn’t verified that sale.

I’d like to return the following:

  • Store name
  • count of sold & bought relationships (store <—> customer)
  • count of only sold relationships (store —> customer)
  • count of only bought relationship (store <— customer)

*** Clarification:

for example, it would be 10 stores with 7-10 customers each, and yes in Neo it would be two different arcs connecting:

store-customer r/ships

the goals is to look at the different reporting practices for each store, as some stores may say they sold when they haven't, and customers may say they bought items. How often do each of those scenarios occur?

Lulu
  • 13
  • 3
  • What are your inputs and expected outputs? Is the query only for a single store and a single customer, or a single store and all its customers? For your outputs, do you want the count across all customers per store, or the counts per customer of the store (where you'll have a row per customer)? – InverseFalcon Mar 26 '19 at 17:07
  • Do really need to have relationships in both directions? In neo4j, traversing a relationship backwards is just as easy and fast as traversing it forwards. So, it would seem you only need one relationship type (either `sold`, or `bought`, but not both). – cybersam Mar 26 '19 at 23:46

2 Answers2

0

You can use something like

match (n:Store)<-[r:bought]-(n1:Customer)<-[r1:sold]-(n) with count(distinct r) as verified 
match (n:Store)<-[r:bought]-(n1:Customer) with verified, count(distinct r) - verified as boughtOnly 
match (n:Store)-[r:sold]->(n1:Customer) with verified, boughtOnly, count(distinct r) - verified as soldOnly 
return verified, soldOnly, boughtOnly

See the dummy database here to test on

EDIT Updated query with where clause and returning store info

match (n:Store) where n.state = 'MA' and n.city = 'Boston' with n as Store
match (Store)<-[r:bought]-(n1:Customer)<-[r1:sold]-(n) with Store, count(distinct r) as verified 
match (Store)<-[r:bought]-(n1:Customer) with Store, verified, count(distinct r) - verified as boughtOnly 
match (Store)-[r:sold]->(n1:Customer) with Store, verified, boughtOnly, count(distinct r) - verified as soldOnly 
return verified, soldOnly, boughtOnly, Store
CIAndrews
  • 1,046
  • 10
  • 19
  • would it be possible to return the store name as well, and to make the cypher query more efficient computationally, insert a where clause on the node property like where n.state = 'MA' and n.city = 'Boston' when I do that I get the following error: Neo.ClientError.Statement.SyntaxError: Variable `n` not defined (line 4, column 7 (offset: 308)) "where n.state = 'MA' and n.city = 'Boston'" – Lulu Apr 03 '19 at 18:33
  • Yes, that's possible, but because the `with` clause only keeps the nodes/relationships stated there, `n` is not available there. You can include the where clause before the different `with` statements or keep the `n` node. But before you continue, have a second look at the comment of @cybersam whether you really need two-way relationships. – CIAndrews Apr 03 '19 at 23:58
0

It's as easy as this : 

MATCH (n:Customer)
RETURN 
size((n)-[:BOUGHT|:SOLD]-()) AS bothRels,
size((n)-[:BOUGHT]-()) AS boughtRels,
size((n)-[:SOLD]-()) AS soldRels

╒══════════╤════════════╤══════════╕
│"bothRels"│"boughtRels"│"soldRels"│
╞══════════╪════════════╪══════════╡
│2         │1           │1         │
└──────────┴────────────┴──────────┘
Christophe Willemsen
  • 19,399
  • 2
  • 29
  • 36