How to improve a mutiple JOIN query in Cypher

Question

I'm looking for the best way to make a some kind of multiple JOIN in my cypher query. I find a solution, but it seems little bit too complex for me. The goal of this query, is by starting from a first node, to retrieve the id and a 'name' property from many other nodes related to the first one by relationships of many types (cf. below)

START c=node(72)
OPTIONAL MATCH (c)<-[:MANAGEMENT_TEAM]-(from_board)
OPTIONAL MATCH (c)<-[:SHAREHOLDER]-(shareholder)
OPTIONAL MATCH (c)-[:PRODUCT_OFFERED]->(product)
OPTIONAL MATCH (c)<-[:CUSTOMER]-(customer)-[:CORE_BUSINESS]->(industry_sector)
OPTIONAL MATCH (c)-[:ASSIGNEE]->(patent)
RETURN [x IN collect(DISTINCT(from_board))| {id:id(x), name:x.name}] AS Management,
       [x IN collect(DISTINCT(shareholder))| {id:id(x), name:x.name}] AS Shareholders,
       [x IN collect(DISTINCT(product))| {id:id(x), name:x.name}] AS Products,
       [x IN collect(DISTINCT(customer))| {id:id(x), name:x.name}] AS Customers,
       [x IN collect(DISTINCT(industry_sector))| {id:id(x), name:x.name}] AS Industry_sectors,
       [x IN collect(DISTINCT(patent))| {id:id(x), name:x.name}] AS Patent

This query returns 6 columns, each of it contains a list of tuple (id & name). Do you see how improve it ? (especially the ugly part "extract/collect/distinct") Or, do you know another manner to do that ? Thanks

Answer 1

one option:

MATCH (c)
WHERE id(c) = 72
OPTIONAL MATCH (c)<-[:MANAGEMENT_TEAM]-(from_board)
WITH c, collect(distinct {id:id(from_board), name:from_board.name}) as Management
OPTIONAL MATCH (c)<-[:SHAREHOLDER]-(shareholder)
WITH c, Management, collect(distinct {id:id(shareholder), name:shareholder.name}) as Shareholders
....
RETURN c, Management, Shareholders, ...

another option

MATCH (c)
WHERE id(c) = 72
OPTIONAL MATCH (c)-[r]-(x)
RETURN c,type(r) as type, collect(distinct {id:id(x), name:x.name}) as connected

Answer 2

Try using schema indexes for your search queries.

it is called INDEXING

indexing makes db search efficience. for create INDEX

CREATE INDEX property(property should be unique)

Answer 3

Thank @Micheal, your second option render little bit more cleaner the cypher query, now it looks like that :

START c=node({companyId})
      OPTIONAL MATCH (c)<-[management_rel:MANAGEMENT_TEAM]-(management)
      OPTIONAL MATCH (c)<-[shareholder_rel:SHAREHOLDER]-(shareholder)
      OPTIONAL MATCH (c)-[:PRODUCT_OFFERED]->(product)
      OPTIONAL MATCH (c)<-[:CUSTOMER]-(customer)-[:CORE_BUSINESS]->(industry_sector)
      OPTIONAL MATCH (c)-[:ASSIGNEE]->(patent)
    RETURN
      c AS company,
      collect(distinct({id:id(management), name:management.name, role:management_rel.role})) AS managements,
      collect(distinct({id:id(shareholder), name:shareholder.name, percentage:shareholder_rel.percentage  })) AS shareholders,
      collect(distinct({id:id(product), name:product.name, description:product.description})) AS products,
      collect(distinct({id:id(customer), name:customer.name, sector_id:id(industry_sector), sector:industry_sector.name}))  AS customers,
      collect(distinct({id:id(patent), name:patent.name, title:patent.title, website:patent.website, description:patent.description})) AS patents

As you see, the request has more joining, like the management relationships added in the 'managaments' tuples. This request takes 183 ms to be executed by the REST API on an EC2 small instance. The combination of the map creation, the distinct and the collect is really powerfull :)