Conditional Where statement if parameter exists

[timojannejokine]

I'm trying to write a query that takes four parameters which are all lists of ids and which should filter the results.

I have nodes called KnowledgeEntry which have multiple KnowledgeRecords attached to them
(:KnowledgeEntry)-[:CONTAINS]->(:KnowledgeRecord)

KnowledgeRecords each have four types of relations to nodes of type Location, Community, Function or OrganizationalUnit which all have an id attribute.

I want to retrieve all KnowledgeEntries if they have one or more KnowledgeRecords that have a relation to Location, Community, Function or OU where id matches one of the items in the given list (parameter)

This is what I came up with so far:

MATCH (knowledgeEntry:KnowledgeEntry)-[relationship]-(:KnowledgeEntry)
WHERE EXISTS {
    MATCH (knowledgeEntry)-[:CONTAINS]->(:KnowledgeRecord)-[:BASED_AT]->(loc:Location)
    WHERE loc.id IN $filters.locationIds
}

RETURN relationship, knowledgeEntry;

1. This only works if all parameters used are set. If for example $filters.locationIds has no entries or is null the where statement should be ignored. I tried this with CASE statement and apoc.when but had no success.

2. When adding an OR EXISTS for e.g OrganizationalUnit I get an error "There should be at least one pattern expression"

MATCH (knowledgeEntry:KnowledgeEntry)-[relationship]-(:KnowledgeEntry)
WHERE EXISTS {
    MATCH (knowledgeEntry)-[:CONTAINS]->(:KnowledgeRecord)-[:BASED_AT]->(loc:Location)
    WHERE loc.id IN $locationIds
}

OR EXISTS {
    MATCH (knowledgeEntry)-[:CONTAINS]->(:KnowledgeRecord)-[:RELEVANT_FOR_EMPLOYEES_WITH]->(ou:OrganizationalUnit)
    WHERE ou.id IN $organizationalUnitIds
}

RETURN relationship, knowledgeEntry;

[MuddyBootsCode]

Have you tried using optional match?https://neo4j.com/docs/cypher-manual/current/clauses/optional-match/

[stefan_armbrust]

Would OPTIONAL MATCH solve your problem?

MATCH (knowledgeEntry:KnowledgeEntry)-[relationship]-(:KnowledgeEntry)-[:CONTAINS]->(kr:KnowledgeRecord)

OPTIONAL MATCH (kr)-[:BASED_AT]->(loc:Location)
WHERE loc.id IN $locationIds

OPTIONAL MATCH (kr)-[:RELEVANT_FOR_EMPLOYEES_WITH]->(ou:OrganizationalUnit)
WHERE ou.id IN $organizationalUnitIds

RETURN knowledgeEntry, relationship, count(*) as numberOfPaths

If the ids of your location, community, function, orgunits don't overlap and your relationship types listed are solely used for relationship to these node types you could do something like:

WITH $locationIds + $organizationalUnitIds as allIds
MATCH (knowledgeEntry:KnowledgeEntry)-[relationship]-(:KnowledgeEntry)-[:CONTAINS]->(kr:KnowledgeRecord)-[:BASED_AT|:RELEVANT_FOR_EMPLOYEES_WITH]->(target)
WHERE target.id in allIds
RETURN knowledgeEntry, relationship, count(*) as numberOfPaths

which is easier to read and is also more performant.

[timojannejokine]

Thank you!
I should have noted that not every KnowledgeEntry does have KnowledgeRecords and not every KnowledgeRecord has Location, Community, Function, OrgUnit. Some KnowledgeRecords have none of those and some only have one or two.

If all of the four parameters are empty or null all KnowledgeEntries should be returned, regardless if they have KnowledgeRecords.
If one or more of those parameters have entries only KnowledgeEntries should be returned that have a KnowledgeRecord that has the respective Location, Community Function or OrgUnit.

So basically the filter should not filter KnowledgeRecords, it should filter KnowledgeEntries but the information to filter after is on the KnowledgeRecord.

I struggled to explain the use-case, I hope it's more clear now.

[stefan_armbrust]

With that clarification I'd use apoc.when to handle the two cases separately. We first build a list of all ids and handle as well cases where an id list might be null via coalesce.

Next is to handle the two cases using my statement from above:

WITH [] + coalesce($locationIds,[]) + coalesce($organizationalUnitIds,[]) as allIds
CALL apoc.when(
   size(allIds)=0
   "MATCH (knowledgeEntry:KnowledgeEntry) RETURN knowledgeEntry",
   "MATCH (knowledgeEntry:KnowledgeEntry)-[relationship]-(:KnowledgeEntry)-[:CONTAINS]->(kr:KnowledgeRecord)-[:BASED_AT|:RELEVANT_FOR_EMPLOYEES_WITH]->(target)
   WHERE target.id in allIds
   RETURN knowledgeEntry, relationship, count(*) as numberOfPaths"
) yield value return value

[EarthlingDavey]

Hi I know I'm grave digging, but I think I found a neat solution to this.

If you change the 'WHERE' line in your first code block to

WHERE ($locationIds IS NOT NULL AND loc.id IN $locationIds) OR ($locationIds IS NULL)

The part before 'OR' is similar to what you already had, but first checks $locationIds has a value.

The part after the 'OR' will be true if $locationIds doesn't have a value. Basically saying WHERE true, and that's the equivelent of having no where statement.

My suggestion above only works when $locationIds is either values or null, but it could be expanded for empty arrays also.