how to find relationships with distinct node

[manjeetthakur]

I have write this query (I'm using Movie DBMS)

MATCH (p) WITH labels(p) as label, count(*) as nCount with collect({label:label, count: nCount}) as nodes
call{
MATCH ()-[relationship]->()
with type(relationship) as r, COUNT(relationship) as rCount
order by rCount desc
return collect({label:r, count: rCount}) as relationships
}
return nodes, relationships

which return this result

[{
  "count": 229,
  "label": [
    "Movie"
  ]
}, 
{
  "count": 803,
  "label": [
    "Person"
  ]
}
]	
----------------------------
[{
  "count": 1036,
  "label": "ACTED_IN"
}
, 
{
  "count": 266,
  "label": "DIRECTED"
}
, 
{
  "count": 90,
  "label": "PRODUCED"
}
, 
{
  "count": 59,
  "label": "WROTE"
}
, 
{
  "count": 53,
  "label": "REVIEWED"
}
, 
{
  "count": 18,
  "label": "FOLLOWS"
}
]

 I want to show high level structural view  now my requirement is  to return source and target

[{
"count": 1036,
"type": "ACTED_IN",
"nodes": [{
                 "source" : "Movies",  // or Can I add id instead of name of the node like "source": "0"
                 "target" : "Person"
            }]
}]

so is there any way I can achieve in cypher query or it is best practices to do in cypher

[Cobra]

Hello @manjeetthakur 😊

I'm not sure but I think this can solve your issue without cypher.

Regards,

Cobra

[manjeetthakur]

@CobraThank you so much for answer

is there any other approach

[glilienfield]

Just a note on your first query.  The labels(p) function returns a list. When you 'count' its values, you are counting the instances of a list. It works in your case, since the labels are singleton lists. You will not get the results you expect if nodes have multiple labels, as you will be counting the number of instances of a specific list, including the order of the elements. You can overcome this by 'unwinding' the labels(p) list, to get a stream of the individual labels. I modified your query to demonstrate it. 

MATCH (p) 
UNWIND labels(p) as label
WITH label, count(*) as nCount 
WITH collect({label:label, count: nCount}) as nodes
call{
MATCH ()-[relationship]->()
WITH type(relationship) as r, COUNT(relationship) as rCount
order by rCount desc
return collect({label:r, count: rCount}) as relationships
}
return nodes, relationships

[
{
  "count": 38,
  "label": "Movie"
}
, 
{
  "count": 133,
  "label": "Person"
}
]

Try the following query to get the relationship node information. 

MATCH (p) 
UNWIND labels(p) as label
WITH label, count(*) as nCount 
WITH collect({label:label, count: nCount}) as nodes
call{
    MATCH ()-[relationship]->()
    WITH type(relationship) as r, startNode(relationship) as startNode, endNode(relationship) as endNode
    WITH r, COUNT(*) as rCount, collect({source: id(startNode), target: id(endNode)}) as nodes
    ORDER BY rCount
    RETURN collect({count: rCount, type: r, nodes: nodes}) as relationships 
}
RETURN nodes, relationships

Example of one relationship:

{
  "nodes": [
    {
      "source": 167,
      "target": 168
    },
    {
      "source": 168,
      "target": 169
    },
    {
      "source": 170,
      "target": 169
    }
  ],
  "count": 3,
  "type": "FOLLOWS"
}

You can expand the node information for each relationship too. 

[manjeetthakur]

@glilienfield  Thank you so much for answering my question

But I only want to show high level view. I only interested to show  single value

like Let suppose we have many nodes  with two unique labels

1. Person

2. Movies

Now I want to show like this

for all relationships ACTED_IN, DIRECTED, PRODUCED, WROTE, REVIEWED, FOLLOWS

One way which is I'm think is in programming language (JavaScript) I'm looping over the relationships and get like this

match x=(a)-[b:ACTED_IN]->(c)
return distinct labels(a), labels(c)

and then prepare new result using JavaScript. But I want to know if there is a way in cypher query to reduce burden in Nodejs side

[glilienfield]

Do you want the list of labels for the source and target nodes for each type of relationship? 

[manjeetthakur]

I want like this

[{
"count": 266,
"type": "DIRECTED"
"node": {
         "source": "Movies",
         "target" : "Person"
  }
},{
  "count": 53,
  "label": "WROTE",
  "node": {
         "source": "Person",
         "target" : "Movies"
  }
}]

[manjeetthakur]

@glilienfield 

{
  "nodes": [
    {
      "source": 165,
      "target": 166
    },
    {
      "source": 166,
      "target": 167
    },
    {
      "source": 168,
      "target": 167
    },
    {
      "source": 336,
      "target": 337
    },
    {
      "source": 337,
      "target": 338
    },
    {
      "source": 339,
      "target": 338
    },
    {
      "source": 507,
      "target": 508
    },
    {
      "source": 508,
      "target": 509
    },
    {
      "source": 510,
      "target": 509
    },
    {
      "source": 678,
      "target": 679
    },
    {
      "source": 679,
      "target": 680
    },
    {
      "source": 681,
      "target": 680
    },
    {
      "source": 849,
      "target": 850
    },
    {
      "source": 850,
      "target": 851
    },
    {
      "source": 852,
      "target": 851
    },
    {
      "source": 1020,
      "target": 1021
    },
    {
      "source": 1021,
      "target": 1022
    },
    {
      "source": 1023,
      "target": 1022
    }
  ],
  "count": 18,
  "type": "FOLLOWS"
}

Instead of this result I'm looking for like this

"count": 18,
"type": "FOLLOWS"
"node": {
         "source": "Movies",
         "target" : "Person"
  }

Thank you so much

[glilienfield · Accepted solution]

Is this it?

MATCH (p) 
UNWIND labels(p) as label
WITH label, count(*) as nCount 
WITH collect({label:label, count: nCount}) as nodes
call{
    MATCH ()-[r]->()
    WITH type(r) as type, startNode(r) as startNode, endNode(r) as endNode
    WITH type, labels(startNode)[0] as startLabel, labels(endNode)[0] as endLabel, COUNT(*) as rCount
    ORDER BY rCount
    RETURN collect({count: rCount, type: type, node: {source: startLabel, target: endLabel}}) as relationships 
}
RETURN nodes, relationships

[
{
  "node": {
"source": "Person",
"target": "Person"
  },
  "count": 3,
  "type": "FOLLOWS"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 9,
  "type": "REVIEWED"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 10,
  "type": "WROTE"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 15,
  "type": "PRODUCED"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 44,
  "type": "DIRECTED"
}
, 
{
  "node": {
"source": "Person",
"target": "Movie"
  },
  "count": 172,
  "type": "ACTED_IN"
}
]

[glilienfield]

BTW- I assumed one label per node. 

[manjeetthakur]

@glilienfield  Thank you so much. Yes one label per node
Thank you so much once again

[glilienfield]

You are welcome.  The secret is to group on the triplet: type, source label and target label, to get the correct count.  Then collect them in the format you desired.