[SOLVED] How can I return a node even when its related objects aren't found?

How can I return a node even when its related objects aren't found?

I am trying to fetch a parent node and its related nodes by id. It works fine but when I try to filter the related nodes by certain criteria the parent node itself is not returned when the related nodes do not pass the given criteria or filters

MATCH (obj{CId:"AT11"})
OPTIONAL MATCH (obj)-[r]->(related)
WHERE related.Created >= "2024-03-03" AND related.Created <= "2024-03-06" 
WITH obj,related
WHERE related.CId CONTAINS 'AT' 
RETURN obj, related

I expect obj to be returned even if related is null. In my case both return null even though obj exists

I tried removing some of the filters and I realized when using only one where clause works fine but using multiple where clause seems to be the issue.

Solution

To always return obj, all the related filtering should be done in the WHERE clause of the OPTIONAL MATCH:

MATCH (obj {CId:"AT11"})
OPTIONAL MATCH (obj)-->(related)
WHERE "2024-03-03" <= related.Created <= "2024-03-06" AND related.CId CONTAINS 'AT'
RETURN obj, related

Note: this query also uses chained comparison for the Created date.

Optional filtering

If you pass parameters for the obj CId ($obj_cid) and the related filtering ($date_range and $rel_cid_substr), and the filtering parameters are optional (that is, can have NULL values), you can use the following:

MATCH (obj)
WHERE obj.CId = $obj_cid
OPTIONAL MATCH (obj)-->(related)
WHERE
  ($date_range IS NULL OR $date_range.start <= related.Created <= $date_range.max) AND
  ($rel_cid_substr IS NULL OR related.CId CONTAINS $rel_cid_substr)
RETURN obj, related