Optional parameters in query, skip clause if param value is missing

[fanka_bacheva]

Hello
I have a recipe database containing Recepies, Ingredients, etc...

I want to do the following query:

:param ingredients  => ["potato", "tomato"];
:param preparationTimeRange => ["15"];
:param skillLevel => ["Easy"];

MATCH (node:Recipe)
WITH node
WHERE any(i in $ingredients WHERE exists((node)-[:CONTAINS_INGREDIENT]->(:Ingredient {name: i})))
OPTIONAL MATCH (node)
WHERE node.preparationTime in $preparationTimeRange
OPTIONAL MATCH (node)
WHERE node.skillLevel in $skillLevel
RETURN node

The problem is that it's ignoring the value of skillLevel.
I know it's because of the optional matching, but how to fix it 🙂
I want to skip this entire clause if there is no value for skillLevel, and take it correctly if there is.

Any clues for building this query?
Thanks! 🙂

[Bennu]

Hi @fanka.bacheva,

What about:

MATCH (node:Recipe)-[:CONTAINS_INGREDIENT]->(i:Ingredient)
WHERE i.name in $ingredients
and node.preparationTimeRange in $preparationTimeRange
and (node.skillLevel is null or node.skillLevel in $skillLevel)
RETURN node, $ingredients, $preparationTimeRange, $skillLevel

You may consider setting the preparationTimeRange as integer in order to effectively establish a range query.

H

[nghia71]

Hi @fanka.bacheva,

@Bennu solution is good. I just change it a bit to reflect the optional choices:

MATCH (node:Recipe)-[:CONTAINS_INGREDIENT]->(i:Ingredient)
WHERE i.name in $ingredients
and (NOT EXISTS(node.preparationTimeRange) OR (EXISTS(node.preparationTimeRange) AND (node.preparationTimeRange IN $preparationTimeRange))
and (NOT EXISTS(node.skillLevel) OR (EXISTS(node.skillLevel) AND (node.skillLevel IN $skillLevel))
RETURN node, $ingredients, $preparationTimeRange, $skillLevel

Hope it helps.