How to count cypher labels with specific condition?

Question

I have a graph database with information about different companies and their subsidiaries. Now my task is to display the structure of the company. This I have achieved with d3 and vertical tree.

But additionally I have to write summary statistics about the company that is currently displayed. Companies can be chosen from a dropdown list which is fetching this data dynamically via AJAX call.

I have to write in the same HTML a short summary like :

Total amount of subsidiaries for CompanyA: 300 Companies in Corporate Havens : 45% Companies in Tax havens 5%

My database consists of two nodes: Company and Country, and the country has label like CH and TH.

CREATE (:TH:Country{name:'Nauru', capital:'Yaren', lng:166.920867,lat:-0.5477})
WITH 1 as dummy MATCH (a:Company), (b:Country) WHERE a.name=‘CompanyA ' AND b.name='Netherlands' CREATE (a)-[:IS_REGISTERED]->(b)

So how can I find amount of subsidiaries of CompanyA that are registered in corporate and tax havens? And how to pass this info further to html

I found different cypher queries to query all the labels as well as apocalyptic.stats but this does not allow me to filter on mother company. I appreciate help.

How does your data model represent a "mother company" versus a "subsidiary", and what relationships are used to connect them? Or is the same Company node used for all "subsidiaries", and the mere fact of being in a different country imply the existence of a separate subsidiary? — cybersam

stdob-- stdob-- · Accepted Answer · 2019-04-05T14:03:45

The cypher is good because you write a query almost in natural language (the query below may be incorrect - did not check, but the idea is clear):

MATCH (motherCompany:Company {name: 'CompanyA'})-[:HAS_SUBSIDIARY]->(childCompany:Company) 
WITH motherCompany, 
     childCompany
MATCH (childCompany)-[:IS_REGISTERED]->(country:Country)
WITH motherCompany, 
     collect(labels(country)) AS countriesLabels
WITH motherCompany, 
     countriesLabels,
     size([countryLabels IN countriesLabels WHERE 'TH' IN countryLabels ]) AS inTaxHeaven
RETURN motherCompany, 
       size(countriesLabels) AS total,
       inTaxHeaven,
       size(countriesLabels) - inTaxHeaven AS inCorporateHeaven

How to count cypher labels with specific condition?

1 Answers