Every few months we run a relational to graph meetup at the Neo London office where we go through how to take your data from a relational database and into the graph.
We use the Northwind dataset which often comes as a demo dataset on relational databases and come up with some queries which seem graph in nature.
My favourite query is one which finds out how employees are organised and who reports to whom. I thought it’d be quite interesting to see what it would look like in Postgres SQL as well, just for fun.
We’ll start off by getting a list of employees and the person they report to:
SELECT e."EmployeeID", e."ReportsTo"
FROM employees AS e
WHERE e."ReportsTo" IS NOT NULL;
EmployeeID | ReportsTo
------------+-----------
1 | 2
3 | 2
4 | 2
5 | 2
6 | 5
7 | 5
8 | 2
9 | 5
(8 ROWS)In cypher we’d do this:
MATCH (e:Employee)<-[:REPORTS_TO]-(sub) RETURN sub.EmployeeID, e.EmployeeID +-------------------------------+ | sub.EmployeeID | e.EmployeeID | +-------------------------------+ | "4" | "2" | | "5" | "2" | | "1" | "2" | | "3" | "2" | | "8" | "2" | | "9" | "5" | | "6" | "5" | | "7" | "5" | +-------------------------------+ 8 rows
Next let’s find the big boss who doesn’t report to anyone. First in SQL:
SELECT e."EmployeeID" AS bigBoss
FROM employees AS e
WHERE e."ReportsTo" IS NULL
bigboss
---------
2
(1 ROW)And now cypher:
MATCH (e:Employee) WHERE NOT (e)-[:REPORTS_TO]->() RETURN e.EmployeeID AS bigBoss +---------+ | bigBoss | +---------+ | "2" | +---------+ 1 row
We still don’t need to join anything so the query isn’t that interesting yet. Let’s bring in some more properties from the manager record so we have to self join on the employees table:
SELECT e."FirstName", e."LastName", e."Title", manager."FirstName", manager."LastName", manager."Title" FROM employees AS e JOIN employees AS manager ON e."ReportsTo" = manager."EmployeeID" WHERE e."ReportsTo" IS NOT NULL FirstName | LastName | Title | FirstName | LastName | Title -----------+-----------+--------------------------+----------+-----+ Nancy | Davolio | Sales Representative | Andrew | Fuller | Vice President, Sales Janet | Leverling | Sales Representative | Andrew | Fuller | Vice President, Sales Margaret | Peacock | Sales Representative | Andrew | Fuller | Vice President, Sales Steven | Buchanan | Sales Manager | Andrew | Fuller | Vice President, Sales Michael | Suyama | Sales Representative | Steven | Buchanan | Sales Manager Robert | King | Sales Representative | Steven | Buchanan | Sales Manager Laura | Callahan | Inside Sales Coordinator | Andrew | Fuller | Vice President, Sales Anne | Dodsworth | Sales Representative | Steven | Buchanan | Sales Manager (8 ROWS)
MATCH (e:Employee)<-[:REPORTS_TO]-(sub) RETURN sub.FirstName, sub.LastName, sub.Title, e.FirstName, e.LastName, e.Title +--------------------------------------------------------------+ | sub.FirstName | sub.LastName | sub.Title | e.FirstName | e.LastName | e.Title | +--------------------------------------------------------------+ | "Margaret" | "Peacock" | "Sales Representative" | "Andrew" | "Fuller" | "Vice President, Sales" | | "Steven" | "Buchanan" | "Sales Manager" | "Andrew" | "Fuller" | "Vice President, Sales" | | "Nancy" | "Davolio" | "Sales Representative" | "Andrew" | "Fuller" | "Vice President, Sales" | | "Janet" | "Leverling" | "Sales Representative" | "Andrew" | "Fuller" | "Vice President, Sales" | | "Laura" | "Callahan" | "Inside Sales Coordinator" | "Andrew" | "Fuller" | "Vice President, Sales" | | "Anne" | "Dodsworth" | "Sales Representative" | "Steven" | "Buchanan" | "Sales Manager" | | "Michael" | "Suyama" | "Sales Representative" | "Steven" | "Buchanan" | "Sales Manager" | | "Robert" | "King" | "Sales Representative" | "Steven" | "Buchanan" | "Sales Manager" | +-------------------------------------------------------------+ 8 rows
Now let’s see how many direct reports each manager has:
SELECT manager."EmployeeID" AS manager, COUNT(e."EmployeeID") AS reports
FROM employees AS manager
LEFT JOIN employees AS e ON e."ReportsTo" = manager."EmployeeID"
GROUP BY manager
ORDER BY reports DESC;
manager | reports
---------+---------
2 | 5
5 | 3
1 | 0
3 | 0
4 | 0
9 | 0
6 | 0
7 | 0
8 | 0
(9 ROWS)MATCH (e:Employee) OPTIONAL MATCH (e)<-[rel:REPORTS_TO]-(report) RETURN e.EmployeeID AS employee, COUNT(rel) AS reports +--------------------+ | employee | reports | +--------------------+ | "2" | 5 | | "5" | 3 | | "8" | 0 | | "7" | 0 | | "1" | 0 | | "4" | 0 | | "6" | 0 | | "9" | 0 | | "3" | 0 | +--------------------+ 9 rows
Things start to get more interesting if we find the transitive reporting relationships that exist. I’m not an expert at Postgres but one way to achieve this is by writing a recursive WITH query like so:
WITH RECURSIVE recursive_employees("EmployeeID", "ReportsTo") AS (
SELECT e."EmployeeID", e."ReportsTo"
FROM employees e
UNION ALL
SELECT e."EmployeeID", e."ReportsTo"
FROM employees e, recursive_employees re
WHERE e."EmployeeID" = re."ReportsTo"
)
SELECT re."ReportsTo", COUNT(*) AS COUNT
FROM recursive_employees AS re
WHERE re."ReportsTo" IS NOT NULL
GROUP BY re."ReportsTo";
ReportsTo | COUNT
-----------+-------
2 | 8
5 | 3
(2 ROWS)If there’s a simpler way let me know in the comments.
In cypher we only need to add one character, ‘*’, after the ‘REPORTS_TO’ relationship to get it to recurse as far as it can. We’ll also remove the ‘OPTIONAL MATCH’ so that we only get back people who have people reporting to them:
MATCH (e:Employee)<-[rel:REPORTS_TO*]-(report) RETURN e.EmployeeID AS employee, COUNT(rel) AS reports +--------------------+ | employee | reports | +--------------------+ | "2" | 8 | | "5" | 3 | +--------------------+ 2 rows
Now I need to find some relational datasets with more complicated queries to play around with. If you have any ideas do let me know.
| Reference: | Northwind: Finding direct/transitive Reports in SQL and Neo4j’s Cypher from our JCG partner Mark Needham at the Mark Needham Blog blog. |
