Adding new node and relationship in a single cypher query

Question

I'm using neo4j 2.1.4 community edition. I have created a some nodes and relationships as below.

Now i wanted acheive the below tasks: I have a node(purple color node) with the properties like ID, Name, Created_Date and End_Date. and it has the relationship property as flag:ACTIVE 1) I wanted to add a node below the Orange color node and above yellow color node i.e., the newly created node will be like a parallel node of purple node with HAS relationship between the orange and yellow color node with a relationship property Status:ACTIVE. 3) Also the existing purple color node relationship property Status:'ACTIVE' should change to INACTIVE and the END_Date of the purple colour node should become the newly created node's Created_Date.

These all things i wanted to acheive in a single query.

I have written the below query to acheive this. I'm able to create a new node with created_date but not able to craete a relationship. I'm not sure where it's going wrong.

Query:

MERGE  (og:OperatingGroup {OperatingGroup_ID:'NOP',OpeartingGroup_Name:'Operating'}) 
ON CREATE SET og.GOG_Start_Date= timestamp()
WITH og
MATCH (h:Hierarchy {Hierarchy_ID:181,Hierarchy_Name:'Global Property Hierarchy'})
Create (h)-[:HAS]->(og)
return h,og;

Things which are not achieved in the above query is: 1)creating relationship with property 2)Updating the END_Date in the old node(purple node) 3)Updating the old node's relationship property to INACTIVE

So how to acheie all thse things in a single cypher query?

Thanks

Answer 1

You should try to translate your words into a query, or first draw it on a paper or whiteboard.

First you want to "MATCH" a node, so why do you start with "MERGE" ?

MATCH (purple:Purple {id:123})
WITH purple

Second, you maybe want to get the orange node and yellow node that are connected to the Purple Node, because your new node should be like a parrallel, you should also give an alias to the relationships as you want to modify the relationships properties later :

MATCH (orange:Orange)-[purpleToOrange:HAS]-(purple)-[purpleToYellow:HAS]-(yellow:Yellow)
WITH purple, orange, yellow, purpleToOrange, purpleToYellow

Then you need to create your new node and attach the yellow and orange nodes to it, the related nodes are already bounded to the query so you can use "MERGE" and avoid duplicated yellow and orange nodes :

MERGE (orange)-[:HAS]->(newPurple:Purple {id: 789654, created_at:20140930140925})<-[:HAS]-(yellow)

Finally you want to update the relationship property status and end_date from the original purple node to ?? (not specified in your description, so I'll assume between purple and orange)

SET purpleToOrange.status = "INACTIVE",
SET purpleToOrange.endDate = 20140930140925

And then you maybe want to return your newly created purple node :

RETURN newPurple

So the complete query now :

MATCH (purple:Purple {id:123})
WITH n
MATCH (orange:Orange)-[purpleToOrange:HAS]-(purple)-[purpleToYellow:HAS]-(yellow:Yellow)
WITH purple, orange, yellow, purpleToOrange, purpleToYellow
MERGE (orange)-[:HAS]->(newPurple:Purple {id: 789654, created_at:20140930140925})<-[:HAS]-(yellow)
SET purpleToOrange.status = "INACTIVE",
SET purpleToOrange.endDate = 20140930140925
RETURN newPurple

Answer 2

You want to do quite a lot with a single query, but I think that this will do it:

MATCH (h:Hierarchy {Hierarchy_ID:181,Hierarchy_Name:'Global Property Hierarchy'})
MERGE (h)-[:HAS{status;'ACTIVE'}]->(og:OperatingGroup {OperatingGroup_ID:'NOP',OpeartingGroup_Name:'Operating'})
ON CREATE SET og.GOG_Start_Date = timestamp()
WITH h, og
MATCH (h)-[has:HAS{status:'ACTIVE'}]->(other:OperatingGroup)
WHERE other <> og
SET has.status='INACTIVE'
SET other.GOG_End_Date = og.GOG_Start_Date
RETURN h, og

Match the Hierarchy node that everything is kicked off from.

MATCH (h:Hierarchy {Hierarchy_ID:181,Hierarchy_Name:'Global Property Hierarchy'})

Merge this with an Active status relationship to the OperatingGroup with the ID 'NOP'.

MERGE (h)-[:HAS{status;'ACTIVE'}]->(og:OperatingGroup {OperatingGroup_ID:'NOP',OpeartingGroup_Name:'Operating'})

If the Group was just created then set the start date.

ON CREATE SET og.GOG_Start_Date = timestamp()

Now match any other OperatingGroups that existed and their HAS relationships. Setting the status property and endDate (on the OperatingGroup node, to match the GOG_Start_Date?) as requested.

WITH h, og
MATCH (h)-[has:HAS]->(other:OperatingGroup)
WHERE other <> og
SET has.status='INACTIVE'
SET other.GOG_End_Date = og.GOG_Start_Date
RETURN h, og

Edit - OperatingCompany

I see from Christophe's answer that he is preserving the OperatingGroup to OperatingCOmpany relationship, which whilst not requested does make a lot of sense (are we operating on your graph upside down?).

MATCH (h:Hierarchy {Hierarchy_ID:181,Hierarchy_Name:'Global Property Hierarchy'})-[existingHas:HAS]->(existingOg:OperatingGroup)<-[belong:BELONGS]-(company:OperatingCompany)
MERGE (h)-[:HAS{status;'ACTIVE'}]->(og:OperatingGroup {OperatingGroup_ID:'NOP',OpeartingGroup_Name:'Operating'})<-[:BELONGS]-(company)
ON CREATE SET og.GOG_Start_Date = timestamp()
WITH h, og, existingHas, exitingOg
SET existingHas.status='INACTIVE'
SET existingOg.GOG_End_Date = og.GOG_Start_Date
RETURN h, og