How to ignore subquery result if it does not match

Question

I am getting started with neo4j and cypher. I have a case where i want to return efforts done on a project grouped by user id and hours for last Week, this week, all time.

My schema is something like this

Project -> Scope -> Effort (userid, hours)

I want to know get how many hours a user has spent in a week. So far i have come up with this query

MATCH (p:Project) where p.id = '1bfc11c8-fb06-4528-9521-cc85db6c6909'
MATCH (p)-[:HAS_SCOPES]-(s:Scope)
MATCH (s)-[:HAS_EFFORTS]-(e:Effort)

CALL {
    with e
    MATCH (e) WHERE date(e.date) >= date("2022-07-18")  AND date(e.date) < date("2022-07-18") + duration({days: 7})
    return e.user_id as thisWeekUserId, sum(e.hours) as thisWeek
}
  CALL {
      WITH e 
    MATCH (e) WHERE date(e.date) >= date("2022-07-18") - duration({days: 7}) AND date(e.date) < date("2022-07-18")
    return e.user_id as lastWeekUserId, sum(e.hours) as lastWeek
  }
return e.user_id as userID, lastWeek, thisWeek

The problem with the above is if the 2nd CALL does not return any rows the whole query will return no results, I have tried it with OPTIONAL MATCH but it returns duplicate records that have wrong result ( as you would have in LEFT JOIN)

Could someone point out what could be the problem.

Answer 1

Try this out:

MATCH (p:Project) where p.id = '1bfc11c8-fb06-4528-9521-cc85db6c6909'
MATCH (p)-[:HAS_SCOPES]-(s:Scope)
MATCH (s)-[:HAS_EFFORTS]-(e:Effort)
WITH COLLECT(e) AS totalEfforts, COLLECT(e1) AS totalEfforts1
UNWIND totalEfforts AS effort
WITH DISTINCT effort.user_id AS userID, [x IN totalEfforts1 WHERE effort.user_id = x.user_id AND date(x.date) >= date("2022-07-18") AND date(x.date) < date("2022-07-18") + duration({days: 7}) | x] AS thisWeekEfforts,
[x IN totalEfforts1 WHERE effort.user_id = x.user_id AND date(x.date) >= date("2022-07-18") - duration({days: 7}) AND date(x.date) < date("2022-07-18") | x] AS lastWeekEfforts
WITH userID, reduce(total = 0, x IN thisWeekEfforts | total + x.hours) AS thisWeek, reduce(total = 0, x IN lastWeekEfforts | total + x.hours) AS lastWeek 
RETURN userID, thisWeek, lastWeek