Columns to rows with count()

[guido]

I have 3 sets of data linked to Producers: Name Variants, Career Steps and Life Events and I want to display the number of nodes for each of them.
The following code produces a nice table with columns, see attachment:

MATCH (p:Producer)-[r:HAS_NAME_VARIANT]->(n:Name)
WHERE n.Name = $neodash_name_name
MATCH (p:Producer)-[r2:HAS_NAME_VARIANT]->(n2:Name) 
MATCH (p)-[r6:CAREERSTEP]-(c:CareerEvent)
MATCH (p:Producer)-[r3:EPISODE]-(l:LifeEvent)
RETURN p AS Totals, count(DISTINCT n2) AS `Name Variants`, count(DISTINCT l) AS `Life Events`, count(DISTINCT c) AS `Career Events`

I am using Neodash; $neodash_name_name is the search term that yields the Producer.

However, since there are more of these types in the making, I want the information to be displayed in rows and not in columns. 
I have tried a few options, a.o. UNWIND, but then all 3 count() integers are added up. 
Any suggestions greatly appreciated!

[glilienfield · Accepted solution]

That is odd the match did not work. It should. I matched the logic in your original code. One difference i see in your fix is that you didn’t specify the node label of ‘x’ as Name. Is Name not correct, and removing it worked? 

Anyways, to answer your question you can remove the following from the map definition on line 9 to remove it entirely from the output.  

totals: p

What does ‘totals’ represent?  It was just the value of ‘p’ in your original query. That made sense since you would want to know which producer the counts corresponds with. Maybe just return the name of the producer instead of the whole node.
do you want to know the total number of Producers for the given name? We can calculate that, but the only way to pass it with your current query results is to repeat the value on each row of data, where each row represents the counts for a specific Producer. 

[glilienfield]

From what I gathered from the query, you want to find all the producers that have a name variant equal to the given input. Then you want to gather statistics for each of those producers. If my interpretation is accurate, I refactored the query to what I think is more efficient. In your approach, you have the three matches sequentially to get the related nodes to count. This will result in a rows that are the Cartesian product of the three results, which will be a lot more rows than you need and the reason you needed to use the 'Distinct' in your counts. In my approach, I perform each match independently and calculate the count for that one relationship. This avoids the Cartesian product and duplicate issue. I gathered the output into a map and returned that. You can just add more matches for each new relationship you have and add the count to the map. The values in the map will be displayed on rows when the json is pretty printed. 

MATCH(x:Name{name:$neodash_name_name})
OPTIONAL MATCH (x)<-[:HAS_NAME_VARIANT]-(p:Producer)
OPTIONAL MATCH (p)-[:HAS_NAME_VARIANT]->(n:Name)
WITH p, count(n) as names
OPTIONAL MATCH (p)-[:CAREERSTEP]-(c:CareerEvent)
WITH p, names, count(c) as careerEvents
OPTIONAL MATCH (p)-[:EPISODE]-(l:LifeEvent)
WITH p, names, careerEvents, count(l) as lifeEvents
RETURN {totals: p, name_variants: names, career_events: careerEvents, life_events: lifeEvents}

If you really want rows, you can do the following to return each value on a separate row. Not sure how pretty you will find it. 

MATCH(x:Name{name:$neodash_name_name})
OPTIONAL MATCH (x)<-[:HAS_NAME_VARIANT]-(p:Producer)
OPTIONAL MATCH (p)-[:HAS_NAME_VARIANT]->(n:Name)
WITH p, count(n) as names
OPTIONAL MATCH (p)-[:CAREERSTEP]-(c:CareerEvent)
WITH p, names, count(c) as careerEvents
OPTIONAL MATCH (p)-[:EPISODE]-(l:LifeEvent)
WITH p, names, careerEvents, count(l) as lifeEvents
WITH {totals: p, name_variants: names, career_events: careerEvents, life_events: lifeEvents} as map
UNWIND keys(map) as key
RETURN key, map[key]

[guido]

@glilienfield : thanks for the almost perfect suggestion!
I had to change the first lines because somehow $neodash_name_name did not catch on and no results were returned:

OPTIONAL MATCH (x)<-[:HAS_NAME_VARIANT]-(p:Producer)
WHERE x.Name = $neodash_name_name
OPTIONAL MATCH (p)-[:HAS_NAME_VARIANT]->(n:Name)
WITH p, count(n) as names
OPTIONAL MATCH (p)-[:CAREERSTEP]-(c:CareerEvent)
WITH p, names, count(c) as careerEvents
OPTIONAL MATCH (p)-[:EPISODE]-(l:LifeEvent)
WITH p, names, careerEvents, count(l) as lifeEvents
WITH {totals: p, `1. Name Variants`: names, `2. Career Events`: careerEvents, `3. Life Events`: lifeEvents} as map
UNWIND keys(map) as key
RETURN key, map[key]
ORDER BY key asc

I then modified the line labels and that produced the result shown in the attachment.
Last point: how can I fix "totals", either by having it show the total (in this case 9) or by omitting the entire line? 
Thanks.

[glilienfield · Accepted solution]

That is odd the match did not work. It should. I matched the logic in your original code. One difference i see in your fix is that you didn’t specify the node label of ‘x’ as Name. Is Name not correct, and removing it worked? 

Anyways, to answer your question you can remove the following from the map definition on line 9 to remove it entirely from the output.  

totals: p

What does ‘totals’ represent?  It was just the value of ‘p’ in your original query. That made sense since you would want to know which producer the counts corresponds with. Maybe just return the name of the producer instead of the whole node.
do you want to know the total number of Producers for the given name? We can calculate that, but the only way to pass it with your current query results is to repeat the value on each row of data, where each row represents the counts for a specific Producer. 

[guido · Accepted solution]

@glilienfield 
For some reason it doesn't work when I add .Name to to x in the first line, although x.Name is parsed correctly in line 2.
Anyhow, it works as it is, so I'm happy.

Removing the totals:p worked perfectly.
I don't need more info since the name of the producer is represented elsewhere, see complete screenprint.
Thanks a million. Again.

[glilienfield]

Should it be ':Name', not '.Name'

Anyways, glad to help...nice dashboard too.