Issue Creating TempArticle in Neo4j Cypher Query

Question

Question:

I'm working on a Neo4j Cypher query that aims to update or create relationships between a store and articles. The goal is to update an existing relationship if its date is older than a new date, and if the relationship does not exist, create a new TempArticle node and establish a relationship. However, the code I've written doesn't seem to be working as expected.

    :param { store_id: 22, article_name: "Candy shop", new_date: "2023-12-14", new_price: 32, article_description: "" };

MERGE (s:Store {store_id: $store_id, name: "STORE DEMO NAME"}); // testing store

MATCH (store:Store {store_id: $store_id})
OPTIONAL MATCH (store)-[rel:HAS_ARTICLE {article_name: $article_name}]->(article)
WITH store, rel, article,
     CASE
         WHEN rel IS NOT NULL AND rel.date < date($new_date) THEN true
         ELSE false
     END AS shouldUpdateRel,
     CASE
         WHEN rel IS NULL THEN true
         ELSE false
     END AS shouldCreateTempArticle
WITH store, rel, article, shouldUpdateRel, shouldCreateTempArticle
WHERE shouldUpdateRel
SET rel.date = date($new_date)
SET rel.prev_price = rel.price
SET rel.price = $new_price 
SET rel.article_name =  $article_name

WITH store, article, shouldCreateTempArticle
WHERE shouldCreateTempArticle
CREATE (tempArticle:TempArticle {article_name: $article_name, article_description: $article_description})
MERGE (store)-[newRel:HAS_ARTICLE]->(tempArticle)
SET newRel.date = date($new_date)
SET newRel.price = $new_price
SET newRel.article_name =  $article_name
RETURN store, article, shouldCreateTempArticle

Problem:

The code is expected to update existing relationships and create a TempArticle node with a new relationship when necessary. However, I've noticed that while the relationship update part seems to work correctly, the TempArticle creation part doesn't behave as expected. Despite the logic suggesting that it should create a TempArticle when shouldCreateTempArticle is true, it doesn't.

Expected Outcome:

When shouldCreateTempArticle is true, a new TempArticle node should be created along with a relationship to the store.

Current Outcome: Despite the logic and conditions being set, the TempArticle node is not being created as expected.

I would appreciate any insights or suggestions on what might be causing this issue and how to resolve it. Thank you in advance for your assistance!

Answer 1

When rel is NULL, then shouldCreateTempArticle will be true and shouldUpdateRel false. But when shouldUpdateRel is false, the WHERE shouldUpdateRel clause in your query aborts the query (for the relevant rows). Therefore, when shouldUpdateRel is false the shouldCreateTempArticle logic that occurs later in your query will never get processed.

Here is a solution that uses two CALL subquery clauses to avoid the two subqueries interfering with each other or the final return value:

MATCH (store:Store {store_id: $store_id})
OPTIONAL MATCH (store)-[rel:HAS_ARTICLE {article_name: $article_name}]->(article)
WITH *,
  rel IS NOT NULL AND rel.date < date($new_date) AS shouldUpdateRel,
  rel IS NULL AS shouldCreateTempArticle
CALL {
    WITH *
    WITH * WHERE shouldUpdateRel
    SET rel.date = date($new_date)
    SET rel.prev_price = rel.price
    SET rel.price = $new_price 
    SET rel.article_name =  $article_name
}
CALL {
    WITH *
    WITH * WHERE shouldCreateTempArticle
    CREATE (tempArticle:TempArticle {article_name: $article_name, article_description: $article_description})
    MERGE (store)-[newRel:HAS_ARTICLE]->(tempArticle)
    SET newRel.date = date($new_date)
    SET newRel.price = $new_price
    SET newRel.article_name =  $article_name
}
RETURN store, article, shouldUpdateRel, shouldCreateTempArticle

The query also returns both flags (shouldUpdateRel and shouldCreateTempArticle) as a hint as to what was done.