Query returning results with nodes that should have been filtered out in WHERE NOT IN {collection}

Neo4j Graph Platform Cypher
1

Hi all, here is the cypher query in question

MATCH (o:Order)-[:CONTAINS_PRODUCT]->(x:Product)-[:HAS_CATEGORY]->(xcat:ProductCategory), (x:Product)-[:HAS_VARIANT]->(v:ProductVariant)
WHERE x.id IN ['8186'] AND x.accountGUID = 'xxxxxxxxx'
WITH min(v.price) as minPrice, collect(distinct xcat.id) as cats, o
MATCH (o)-[:CONTAINS_PRODUCT]->(p:Product)
WHERE NOT p.id IN ['8186']  AND NOT p.id IN ['8369', '9540', '9557', '9574', '9591']
WITH minPrice, cats, p, count(*) AS score
MATCH (p)-[:HAS_CATEGORY]->(c:ProductCategory), (p)-[:HAS_TAG]->(tag)
WHERE c.id IN cats AND tag.name IN ['men\'s'] AND NOT c.name IN ['Gift', 'Cards', 'Socks', 'Flash Sale', 'Socks', 'Socks', 'Socks', 'Special Pricing', 'Clearance Locker', 'Circuit Cross-Training Shoe']
MATCH (p)-[:HAS_VARIANT]->(v:ProductVariant)
WHERE v.price >= minPrice AND v.inventoryQuantity <> -9999
WITH
    p.id as ID,
    p.optionType1 as ot1,
    p.optionType2 as ot2,
    p.optionType3 as ot3,
    p.url as url,
    p.title as title,
    p.imageURL1 as imageURL1,
    p.imageURL2 as imageURL2,
    p.imageURL3 as imageURL3,
    p.price AS price,
    p.msrp AS msrp,
    CASE WHEN p.msrp = 0 THEN 0.0 ELSE (p.price/p.msrp) * 100 END as discount,
    score,
    collect(distinct {
        id: v.id,
        imageURL1: v.imageURL1,
        imageURL2: v.imageURL2,
        imageURL3: v.imageURL3,
        sku: v.sku,
        title: v.title,
        price: v.price,
        msrp: v.msrp,
        inv: v.inventoryQuantity,
        option1: v.option1,
        option2: v.option2,
        option3: v.option3
    }) as variantList,
    MAX(v.inventoryQuantity) AS q
WHERE q > 0
RETURN
        ID,
        ot1,
        ot2,
        ot3,
        url,
        title,
        imageURL1,
        imageURL2,
        imageURL3,
        price,
        msrp,
        discount,
        sum(score) as score,
        variantList
    ORDER BY score DESC
    LIMIT 10

The where clause in question is this one

WHERE c.id IN cats AND tag.name IN ['men\'s'] AND NOT c.name IN ['Gift', 'Cards', 'Socks', 'Flash Sale', 'Socks', 'Socks', 'Socks', 'Special Pricing', 'Clearance Locker', 'Circuit Cross-Training Shoe']

The result is correctly getting the products tagged with Men's, however it is not filtering out products that have a product category of Clearance Locker. I have tried a couple different things, such as splitting up the WHERE clause into two separate MATCH executions, one for products-tags filter and one for product-categories. I have also tried passing down the product reference using WITH statements throughout the cypher query, however both still returned products with Clearance Locker.

Any help is much appreciated in any regard!

2

Hi,
Have you checked case-sensitive in 'Clearance Locker'?
match (c: ProductCategory) where c.name = 'Clearance Locker' return c
Alessio

3

Also watch out for trailing whitespace, or non-visible characters (possibly due to the format of the file you imported from).