基于地图参数的 Neo4j Cypher 过滤 [英] Neo4j Cypher filtering based on map parameters

问题描述

基于上一个问题:

Neo4j Cypher 查询结构和性能优化Neo4j Cypher 节点通过模式理解过滤

最后，我将查询重构为以下内容:

finally I have refactored my query to the following:

MATCH (parentD)-[:CONTAINS]->(childD:Decision) 
    WHERE id(parentD) = {decisionId} 
        MATCH (childD)<-[:SET_FOR]-(equalFilterValue)-[:SET_ON]->(equalFilterCharacteristic) 
            WHERE ALL(key IN keys({equalFilters}) WHERE id(equalFilterCharacteristic) = toInt(key) AND equalFilterValue.value = ({equalFilters}[key]))  
    WITH DISTINCT childD  
        MATCH (childD)<-[:SET_FOR]-(rangeFilterValue)-[:SET_ON]->(rangeFilterCharacteristic) 
            WHERE ALL(key IN keys({rangeFilters}) WHERE id(rangeFilterCharacteristic) = toInt(key) AND ({rangeFilters}[key])[0] <= rangeFilterValue.value <= ({rangeFilters}[key])[1]) 
WITH * MATCH (childD)-[ru:CREATED_BY]->(u:User)  
RETURN ru, u, childD AS decision 
SKIP 0 LIMIT 100

如果每个 *filter 类型(映射)只有一个键，这个查询就可以正常工作，例如:

This query works fine if each *filter type(map) has only one key, for example:

queries.add(new InQuery(integerCharacteristic.getId(), 30));

或

queries.add(new InQuery(stringCharacteristic.getId(), "Two"));

但是当我添加 2 个或更多条件时失败，例如:

but fails when I add 2 or more conditions, for example:

queries.add(new InQuery(integerCharacteristic.getId(), 30));
queries.add(new InQuery(stringCharacteristic.getId(), "Two"));

以下查询未按预期工作并且我的测试断言失败:

The following query doesn't work as expected and my test assertions fail:

MATCH (parentD)-[:CONTAINS]->(childD:Decision) 
WHERE id(parentD) = {decisionId} 
    MATCH (childD)<-[:SET_FOR]-(inFilterValue)-[:SET_ON]->(inFilterCharacteristic) 
        WHERE ALL(key IN keys({inFilters}) WHERE id(inFilterCharacteristic) = toInt(key) AND ({inFilters}[key]) IN inFilterValue.value) 
WITH * MATCH (childD)-[ru:CREATED_BY]->(u:User)  
RETURN ru, u, childD AS decision 
SKIP 0 LIMIT 100

参数:

inFilters = {3153=30, 3151=Two}

当 inFilters 映射包含 2 个或更多键时为什么它不起作用以及如何使其工作?

Why it doesn't work when inFilters map contains 2 or more keys and how to make it working ?

推荐答案

因此，由于这只适用于 1 个键，让我们来看看当有 2 个键时，WHERE ALL 正在做什么.

So for the reason this only works with 1 key, lets go over what one WHERE ALL is doing when there is 2 keys.

WITH inFilters = {3153=30, 3151=Two}
....
WHERE ALL(key IN keys({equalFilters}) 
    WHERE id(equalFilterCharacteristic) = toInt(key) 
    AND equalFilterValue.value = ({equalFilters}[key]))

相当于

WHERE id(equalFilterCharacteristic) = toInt(3153) 
AND equalFilterValue.value = ({equalFilters}[3153]) 
AND id(equalFilterCharacteristic) = toInt(3151) 
AND equalFilterValue.value = ({equalFilters}[3151])

问题在于，现在我们正在检查每个 equalFilterCharacteristic 的节点 id 是否同时等于 3153 和 3151.由于 Neo4j 只使用实数作为节点 id，当有超过 1 个键时，上述语句基本上最终会减少到昂贵的 WHERE FALSE.因此，在上述情况下，WHERE ALL 永远不会为真.如果至少有 1 个检查组为真，则 WHERE ANY 将评估为真，并且等效于

And the problem there is that now we are checking that the node id of equalFilterCharacteristic is equal to 3153 AND 3151 at the same time, for each and every equalFilterCharacteristic. Since Neo4j only uses real numbers for the node ids, the above statement basically ends up reducing to an expensive WHERE FALSE when there is more than 1 key. So WHERE ALL can never be true in the above case. WHERE ANY however will evaluate to true if at least 1 check-group is true, and would be equivalent to

WHERE ( 
    id(equalFilterCharacteristic) = toInt(3153) 
    AND equalFilterValue.value = ({equalFilters}[3153]) 
)
OR 
(
    id(equalFilterCharacteristic) = toInt(3151) 
    AND equalFilterValue.value = ({equalFilters}[3151]) 
)

当然，既然你知道你要匹配的键，你可以跳过 ALL 直接做(不要认为你需要在 id() 上使用 toInt()，但 id() 很长)

Of course, since you know the key you are trying to match, you can skip the ALL and just do (don't think you need toInt() on id(), but id() is a long)

WHERE equalFilterValue.value = ({equalFilters}[id(equalFilterCharacteristic)]) 

这篇关于基于地图参数的 Neo4j Cypher 过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！