Joining two subqueries with Cypher in Neo4J

Question

I have a following SQL query:

SELECT q1.customerId, q1.invoiceId, q2.workId, sum(q2.price)
FROM (select customer.id as customerId, invoice.id as invoiceId, work.id as workId from customer, invoice, workinvoice, work where customer.id=invoice.customerid and invoice.id=workinvoice.invoiceId and workinvoice.workId=work.id
) as q1, (select work.id as workId, sum((price * hours * workhours.discount) + (purchaseprice * amount * useditem.discount)) as price from worktype,workhours,work,warehouseitem,useditem where worktype.id=workhours.worktypeid and workhours.workid=work.id and work.id=useditem.workid and useditem.warehouseitemid=warehouseitem.id group by work.id
) as q2
WHERE q1.workId = q2.workId group by q1.invoiceId;

This query should return me a sum of work prices for each invoice per customer.

I would be interested to know how to do this kind of query in Neo4J. I know that there is UNION https://neo4j.com/docs/cypher-manual/current/clauses/union/. However that does seem to do what I want. I need to make two subqueries and join them from same node as in that SQL example. What would be the correct way to do this with Cypher?

Can you clarify which tables the following are from: price, hours, purchaseprice, and amount? Also, which of these tables are just join tables? You haven't provided a graph model of this data, so we'll need some extra info from you to figure out what's relevant and should be included, and what's not and should be excluded from a graph data model. — InverseFalcon
Some verbal description of the tables involved and the relevant data per table would help. — InverseFalcon
Also if you can point out which tables are joined 1-1 and which are 1-many, that would also help — InverseFalcon
Table definitions can be seen in github.com/homebeach/graafitietokantaprojekti/blob/master/src/…. Take a look at the createTables() method. — homebeach

FrobberOfBits FrobberOfBits · Accepted Answer · 2020-01-28T12:51:29

There's a quite complex example of how to do a join in cypher which you can find here: https://github.com/moxious/halin/blob/master/src/api/data/queries/dbms/3.5/tasks.js#L22

Basically, the technique is that you run the first query, collect the results. Then you run the second, collect the results. Then you unwind the second, match using a filter, and return the result.

In really simplified form, it looks something like this:

CALL something() YIELD a, b
WITH collect({ a: a, b: b }) as resultSet1
CALL somethingElse YIELD a, c
WITH resultSet1, collect({ a: a, c: c }) as resultSet2

UNWIND resultSet2 as rec
WITH [item in resultSet1 WHERE item.a = rec.a][0] as match, rec

RETURN match.a, match.b, rec.c

The list comprehension bit is basically doing the join. Here we're joining on the "a" field.

Joining two subqueries with Cypher in Neo4J

2 Answers