CODEWITHSUNDEEP
neo4j
DanielSF
DanielSF

Reputation: 23

How to 'flatten' the result in Neo4j cypher?

I have the following tables (similar to Northwind sample database):

Employee->OrdersWithDetails<-Products tables
Employee(includes employee-manager relationship with 4 levels)
Employee->EmployeeLvls - links employee's level with specific attributes tied to his level
OrdersWithDetails combines Orders & OrderDetails into one set.

For each employee, I’m trying to display employee's orders and $$; i.e. OrderId, SoldAmt and employee’s managers UP the line.

There should be a SINGLE line for the combination of employee and OrderId, but so far I was only able to display multiple lines; for each level -additional line with manager's name is added.

Here is my cypher *

 MATCH (e:Employees)-[:JOB_LEVEL_OF]->(el:EmployeeLvls)
where e.Name ="Vidur Luthra" //limiting to a single employee
MATCH (p:Products)-[:PRODUCT]->(owd:OrdersWithDetails)<-[:SOLD]-(e)-[:REPORTS_TO*0..4]->(m:Employees)-[:JOB_LEVEL_OF]->(el_m:EmployeeLvls)
WHERE m.EmployeeId <> e.EmployeeId  //excluding employee
RETURN e.Name as Employee ,owd.OrderId 
,SUM(owd.ProductQuantity*p.UnitPrice) as SoldAmt //display manager's names based on their level:
,(CASE WHEN m.EmployeeLvlId = e.EmployeeLvlId - 1 THEN m.Name ELSE '-' END) as Manager_1_LevelAbove
,(CASE WHEN m.EmployeeLvlId = e.EmployeeLvlId - 2 THEN m.Name ELSE '-' END) as Manager_2_LevelsAbove
,(CASE WHEN m.EmployeeLvlId = e.EmployeeLvlId - 3 THEN m.Name ELSE '-' END) as Manager_3_LevelsAbove

*

Here is my outcome: current result or

Employee|OrderId|SoldAmt |Manager_1_LevelAbove|Manager_2_LevelsAbove| Manager_3_LevelsAbove
Vidur Luthra|94 |733.49  |James Kramer        |                     | 
Vidur Luthra|94 |733.49  |                    |                     | Ken Sanchez
Vidur Luthra|94 |733.49  |                    |Jossef Goldberg      |

Here is my desired outcome: desired result or

Employee|OrderId|SoldAmt |Manager_1_LevelAbove|Manager_2_LevelsAbove| Manager_3_LevelsAbove
Vidur Luthra|94 |733.49  |James Kramer        |Jossef Goldberg      | Ken Sanchez

What should I do?

Upvotes: 2

Views: 651

Answers (2)

Brian Underwood
Brian Underwood

Reputation: 10856

Another answer to address your comment:

MATCH
  (e:Employees)-[:JOB_LEVEL_OF]->(el:EmployeeLvls)
WHERE e.Name ="Vidur Luthra" //limiting to a single employee
MATCH
  (p:Products)-[:PRODUCT]->(owd:OrdersWithDetails)<-[:SOLD]-(e),
  reporting_path=(e)-[:REPORTS_TO*0..4]->(m:Employees)
WHERE
  NOT((m)-[:REPORTS_TO]->()) AND
  m.EmployeeId <> e.EmployeeId  //excluding employee


WITH e, owd, p, nodes(reporting_path) AS managers
UNWIND managers AS manager
MATCH (manager)-[:JOB_LEVEL_OF]->(el_m:EmployeeLvls)

WITH e, owd, p, collect({Name: manager.Name, JobTitle: el_m.JobTitle}) AS managers

RETURN
  e.Name as Employee,
  owd.OrderId,
  SUM(owd.ProductQuantity * p.UnitPrice) as SoldAmt,
  (managers[0]).Name AS Manager_1_LevelAbove,
  (managers[0]).JobTitle AS Manager_1_JobTitle,
  (managers[1]).Name AS Manager_2_LevelAbove,
  (managers[1]).JobTitle AS Manager_2_JobTitle,
  (managers[2]).Name AS Manager_3_LevelAbove
  (managers[2]).JobTitle AS Manager_3_JobTitle

Upvotes: 0

Brian Underwood
Brian Underwood

Reputation: 10856

I think that the best way to do this would be to define a variable for the part of the path that you care about and then use that path:

MATCH
  (e:Employees)-[:JOB_LEVEL_OF]->(el:EmployeeLvls)
WHERE e.Name ="Vidur Luthra" //limiting to a single employee
MATCH
  (p:Products)-[:PRODUCT]->(owd:OrdersWithDetails)<-[:SOLD]-(e),
  reporting_path=(e)-[:REPORTS_TO*0..4]->(m:Employees)
  (m)-[:JOB_LEVEL_OF]->(el_m:EmployeeLvls)
WHERE
  NOT((m)-[:REPORTS_TO]->()) AND
  m.EmployeeId <> e.EmployeeId  //excluding employee
RETURN
  e.Name as Employee,
  owd.OrderId,
  SUM(owd.ProductQuantity * p.UnitPrice) as SoldAmt,
  (nodes(reporting_path)[0]).Name AS Manager_1_LevelAbove,
  (nodes(reporting_path)[1]).Name AS Manager_2_LevelAbove,
  (nodes(reporting_path)[2]).Name AS Manager_3_LevelAbove

Note here that I put in a WHERE clause for NOT((m)-[:REPORTS_TO]->()). This means that if Jim reports to Sally who reports to Bill and Bill reports to nobody, we're only going to MATCH on the path between Jim and Bill, not the one between Jim and Sally. That way we don't get duplicates like you're seeing.

Upvotes: 1

Related Questions