How to filter by an optional relationship parameter in cypher?

Question

There are organizations and applications within the software system. User actions generate log records. Log records (green) do belong to organizations (red) and may belong to applications (pink).

There is a need to filter log records by a various set of parameters (creation time, author, organization etc). Let's filter log records by the application they belong to and enlist all log records in the system if this parameter is not specified. To do this I created the following query:

MATCH
(log0:Log)-[:GENERATED_IN]->(org:Organization),
(a1:Application)<-[:GENERATED_IN]-(log1:Log)
WHERE ID(a)=coalesce({applicationID}, ID(a))
RETURN DISTINCT
CASE {applicationID}
    WHEN null THEN log0
    ELSE log1
END
as result

Because the parameter is optional, I need to handle the situation when it is not specified. I decided to use ID(a)=coalesce({applicationID}, ID(a)) for this case, what will be equal to ID(a)=ID(a) in case the applicationID param is not specified (so we just don't filter by this parameter, what I was going to actually achieve).

I wonder if there is more elegant way to do this filtering by optional relationship. I tried

MATCH
(a:Application)<-[:GENERATED_IN*0..1]-(log:Log)-[:GENERATED_IN]->(org:Organization)
WHERE ID(a)=coalesce({applicationID}, ID(a))
RETURN 
log

And also tried queries with OPTIONAL MATCH, but without success. I would appreciate suggestions for improvement.

And BTW, is there a way to specify parameters like {applicationID} from the neo4j browser console? Like <query>, {applicationID}=202175. I substitute values manually every time.

"is there a way to specify parameters [...]?" It's not very well advertised, but there is the `:param` command. See https://stackoverflow.com/questions/42397773/neo4j-what-is-the-syntax-to-set-cypher-query-parameters-in-the-browser-interfac — Gabor Szarnyas, Nov 13 '17 at 20:32

Gabor Szarnyas · Accepted Answer · 2017-11-13T20:51:03.890

Instead of coalesce, you could use IS NULL and check if the parameter was provided:

MATCH
  (log0:Log)-[:GENERATED_IN]->(org:Organization),
  (a1:Application)<-[:GENERATED_IN]-(log1:Log)
WHERE {applicationID} IS NULL
   OR ID(a1)={applicationID}
RETURN DISTINCT
  CASE {applicationID}
    WHEN null THEN log0
    ELSE log1
  END
  AS result

If using Neo4j 3.2+, I'd also consider switching to the new parameter syntax, $param instead of {param}, which makes the queries slightly easier to read.

How to filter by an optional relationship parameter in cypher?

1 Answers1