Call custom procedure ON CREATE

[AlbertGevorgyan]

I am trying to merge a node, and then pass it to a custom procedure - but only the first time it is created. However, this doesn't work:

MERGE (n:Any {id:1}) 
ON CREATE WITH n CALL my.procedure(n)

Is there an alternative way to do this?

[david_allen · Accepted solution]

Unfortunately, ON CREATE / ON MATCH are for setting additional properties, not arbitrary sub-queries as you're attempting to do here.

A way that you could work around this is to use datetimes in a "created" or "modified" datestamp, and let the procedure know that way. Example:

WITH datetime() as now
MERGE (n:Any { id: 1 })
    ON CREATE SET n.created = now
WITH n
CALL my.procedure(n, now)

Notice that inside of the procedure, the node n will have a "created" timestamp; the second argument will be the timestamp assigned "on create".

If those values match, the node was created. If they differ, the node was merged.

[AlbertGevorgyan]

Thanks for your reply. Since my procedure itself calculates and sets a property, the simplest solution is to check if the property already exists in the procedure's body - the only downside is that this logic is hidden from the user.

The other alternative is to let the procedure yield the property and then set it explicitly:

CALL my.calculatingProcedure() YIELD value
WITH value 
MERGE (n:Any {id:1}) 
ON CREATE SET n.property = value;

This is clean but overly verbose. Also, the procedure (which has a side effect) is called regardless whether the node is created or matched. It would be nice to be able to call procedures in ON CREATE or ON MATCH clauses.

[glilienfield]

You could try something like the following. The WITH clause will pass the node through only if it was created, otherwise the query terminates. The following assumes your_procedure is a procedure. Can you write it as a function, so you can call and assign it directly?

MERGE (n:Any {id:1})
ON CREATE SET n.created = true
WITH [i in [n] where i.created = true][0] as node
CALL {
CALL your_procedure() yield value
RETURN value
}
SET node.property = value
REMOVE node.created

[AlbertGevorgyan]

Thanks, this works. Would it be possible then to continue the query with ON SET and RETURN clauses?
A RETURN in the end of this query only returns a newly created node, or null if it was matched.

[glilienfield]

Yes you can continue with WITH clause or RETURN. You are correct, the query does not get passed the WITH clauses for an existing node, since the result of [i in [n] where i.created = true] is empty.

The above is kind of a convoluted implementation of an 'if' clause. Do you want to be able to continue regardless of new or existing, but only set the value for new?

[AlbertGevorgyan]

I need to set multiple properties in ON CREATE (no just the one calculated by the procedure), and then return the node. So ideally it should be something like:

MERGE (n:Any {id:1}) 
ON CREATE SET n.property = {value returned by a side-effectful procedure}
ON CREATE SET n.otherProperty = otherValue
RETURN n

[glilienfield]

You can set as many properties as you want. The above solution only sets properties on non-existing nodes. As you stated, adding a RETURN n at the end will return the new node and null for an existing node.

The following seems to work. It only calls and sets properties in the subquery for new nodes. The query doesn't stop for existing nodes, because the check is within the subquery. It will return any node. You can also continue processing the node too, allowing to set properties for both new and existing nodes, or any other operations you want to chain to this query.

MERGE (n:Any {id:1})
ON CREATE SET n.created = true
WITH n
CALL {
WITH n
WITH [i in [n] where i.created = true][0] as node
CALL your_procedure() yield value
SET node.property = value, node.property2=value2 (set only for new nodes)
REMOVE node.created
}
SET n.property3=value3 (will be set for new and existing nodes)
RETURN n

[AlbertGevorgyan]

Yes, this works indeed, thanks! To slightly simplify it, other (non-calculated) properties can be set directly in the ON CREATE clause:

MERGE (n:Any {id:1})
ON CREATE SET n.created = true, n.property2=value2 
WITH n
CALL {
  WITH n  
  WITH [i in [n] where i.created = true][0] as node
  CALL your_procedure() yield value
  SET node.property = value
  REMOVE node.created
}
SET n.property3=value3 (will be set for new and existing nodes)
RETURN n

[glilienfield]

yeah, totally correct. I overlooked that.