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

Question

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.

Answer 1

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