How to combine in and out relationship

[dariusaudryc]

Hi,

I want to separate going in and going out relationships in a single cypher query, but it seems that I failed. My query is in this format:

match (p:Person) where p.attribute = 'some attribute' with p match (p)-[r:Rel_A]->(d), (p)<-[s:Rel_A]-(c) return r.date, count(r.number_messages_sent) , count(s.number_messages_received) order by r.date

Which works, but the result is different than when I run it one at a time:
match (p:Person) where p.attribute = 'some attribute' with p match (p)-[r:Rel_A]->(d) return r.date, count(r.number_messages_sent) order by r.date
and
match (p:Person) where p.attribute = 'some attribute' with p match (p)<-[s:Rel_A]-(c) return s.date, count(s.number_messages_received) order by r.date

Am I doing something wrong, and can anyone shed some light if I want to combine these two going in and going out relationships?

[jsmccrumb]

The first query will require p:Person to have both an incoming and outgoing Rel_A. If you want to capture both the case when there is only incoming or only out going AND when there is both try using an OPTIONAL MATCH

match (p:Person) where p.attribute = 'some attribute' with p OPTIONAL MATCH (p)-[r:Rel_A]->(d), (p)<-[s:Rel_A]-(c) return r.date, count(r.number_messages_sent) , count(s.number_messages_received) order by r.date

[dariusaudryc]

jsmccrumb:

r only out going AND wh

Hey thanks for your answer. I still didn't get the right value. It seems that when I do the join, it combines both the in going and the out going.

[andrew_bowman]

Are you trying to get the combined counts regardless of direction? If so, omit the direction in your MATCH:

match (p:Person)-[r:Rel_A]-(d) 
where p.attribute = 'some attribute' 
return r.date, count(r) 
order by r.date

[dariusaudryc]

Hey,

No, what I want to do is to seperate them, but have them in one query. I want to get a table of:
for a user, what is the number of outgoing relationship, and in coming relationship per day. I just wonder if I can do it in one cypher query.

[andrew_bowman]

Ah, okay, that's a bit of extra work, but doable. We can start off the same way and then get the counts based on the day and direction, then sum per date using CASE so we get both incoming and outgoing counts for each date on the same row:

MATCH (p:Person)-[r:Rel_A]-(d) 
WHERE p.attribute = 'some attribute' 
WITH r.date as date, p = endNode(r) as isIncoming, count(r) as count
WITH date, sum(CASE WHEN isIncoming THEN count ELSE 0 END) as incoming, sum(CASE WHEN NOT isIncoming THEN count ELSE 0 END) as outgoing
ORDER BY date
RETURN date, incoming, outgoing

[dariusaudryc]

Ah it works!!! Thank you so much for your help Andrew. I didn't know that there is a function endNode.

By the way, why p = endNode(r), isn't p variable have been used for Person node? Where can I read more about the logic of this cypher to understand the reasoning so I can use it in the future?

[andrew_bowman]

Yes, p is the variable bound to the :Person node from the previous match. What we're doing there is getting the direction of the relationship as a boolean (true if incoming to p, false otherwise) by checking that the end node of the relationship (where it's pointing) is p.

Here's a link to our built-in functions, including startNode() and endNode().

[jsmccrumb]

Are you looking for a count of the relationships by day or a sum of a value on the relationships?

dariusaudryc:

It seems that when I do the join, it combines both the in going and the out going.

What was the cypher you tried?

[dariusaudryc]

This is the cypher tried:
match (p:USER) where p.attribute = 'some attribute' with p OPTIONAL MATCH (p)-[r:Rel_A]->(d), (p)<-[s:Rel_A]-(c) return r.trx_date as date_trx, count(r) as num_given, count(s) as num_received order by date_trx

To answer your question, yes, I want to count number of relationship (I believe the sum will be the same if I get the count right) by day for a given user.