Code Monkey home page Code Monkey logo

Comments (2)

thobe avatar thobe commented on June 11, 2024

Questions about using Cypher are better directed at a user support forum such as StackOverflow or the Neo4j online community: https://community.neo4j.com/

If we start from your query, the main reasons I see for it not working is that there are typos and data type issues in the query.

The first issue that prevents things from working is that there is no status variable in scope to insert in the prevData map projection: p{.*, status} as prevData, so let's drop that to make p{.*} as prevData.

This leads us to the second issue, where there is a predicate based on prevData.status: (prevData.status <> m.status), and in the case where p is null prevData will be null which will have the predicate evaluate to null, and that isn't something apoc.do.when seems to be able to handle. So we need to ensure that when p is null, we provide a default for prevData that contains some default value for status, like so: coalesce(p{.*},{status:''}) as prevData (I don't know if the empty string is a sensible default value for prevData.status, since I don't know your data domain, so you will probably have to tune that).

Once we have the primary query, we find that the query passed to apoc.do.when has a comma missing in between userId and rowStatusId. After we fix that, the query becomes:

UNWIND [{userId:'123', firstName:'Devika',status:'Active'}, {userId:'123', firstName:'Devika',status:'Suspended'},  {userId:'123', firstName:'Devika',status:'Terminated'}] as users 
OPTIONAL MATCH(p:USER{userId:users.userId})
WITH p, coalesce(p{.*},{status:''}) as prevData, users
MERGE (m:USER { userId:users.userId })
ON CREATE SET  m.firstName=users.firstName,
m.status = users.status  
ON MATCH SET  m.firstName=users.firstName,
m.status = users.status 
with prevData, m
CALL apoc.do.when((prevData.status <> m.status), "CREATE (his:UserHistory{historyCode:\"DSC\",oldValue:prevData.status,newValue:m.status, userId:m.userId, rowStatusId:1})" ,"return null", {prevData:prevData, m:m}) yield value
return value

Which creates the following 4 nodes (when run on an empty graph):

╒═══════════════╤═══════════════════════════════════════════════════════════════════════════════════════════╕
│"labels(n)"    │"properties(n)"                                                                            │
╞═══════════════╪═══════════════════════════════════════════════════════════════════════════════════════════╡
│["USER"]       │{"firstName":"Devika","userId":"123","status":"Terminated"}                                │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Active","historyCode":"DSC","rowStatusId":1,"oldValue":"","userId":"123"}     │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Suspended","historyCode":"DSC","rowStatusId":1,"oldValue":"","userId":"123"}  │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Terminated","historyCode":"DSC","rowStatusId":1,"oldValue":"","userId":"123"} │
└───────────────┴───────────────────────────────────────────────────────────────────────────────────────────┘

This makes it fairly obvious that we are getting the default value for prevData.status in every UserHistory node, and I am guessing that is the problem you actually wanted to resolve.

This is by design, where the specification of Cypher dictates that the effects of a later clause may not influence the result of a prior clause. In this case the MERGE that creates the USER nodes may not influence the MATCH that finds p. This means that the new nodes you create will never be seen by your OPTIONAL MATCH(p:USER{userId:users.userId}).

We can use a subquery to ensure that we run the entire set of changes for one user before processing the next user:

UNWIND [{userId:'123', firstName:'Devika',status:'Active'}, {userId:'123', firstName:'Devika',status:'Suspended'},  {userId:'123', firstName:'Devika',status:'Terminated'}] AS user
CALL { // Use a subquery to ensure that all of this runs per row before the next row
  WITH user
  OPTIONAL MATCH(p:USER{userId:user.userId})
  WITH coalesce(p.status,'') AS prevStatus, user
  MERGE (m:USER{userId:user.userId})
  // Since we set the same thing on both MATCH and CREATE, we don't need for the SET to be conditional
  SET m.firstName = user.firstName, m.status = user.status
  RETURN prevStatus
}
CREATE (:UserHistory{historyCode:'DSC', oldValue:prevStatus, newValue:user.status, userId:user.userId, rowStatusId:1})

Which gives us this result:

╒═══════════════╤═══════════════════════════════════════════════════════════════════════════════════════════════════╕
│"labels(n)"    │"properties(n)"                                                                                    │
╞═══════════════╪═══════════════════════════════════════════════════════════════════════════════════════════════════╡
│["USER"]       │{"firstName":"Devika","userId":"123","status":"Terminated"}                                        │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Active","historyCode":"DSC","rowStatusId":1,"oldValue":"","userId":"123"}             │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Suspended","historyCode":"DSC","rowStatusId":1,"oldValue":"Active","userId":"123"}    │
├───────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────┤
│["UserHistory"]│{"newValue":"Terminated","historyCode":"DSC","rowStatusId":1,"oldValue":"Suspended","userId":"123"}│
└───────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────┘

Please direct any follow up questions to a user support forum such as StackOverflow or the Neo4j online community: https://community.neo4j.com/

from opencypher.

Devikaelangovan avatar Devikaelangovan commented on June 11, 2024

@thobe
Thank you very much.
It helps a lot to understand how the cypher query works!

from opencypher.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.