Trying to perform multiple (and optional) MERGE using WITH from an apoc.load.jdbc call

apoc
cypher

(Paul Drangeid) #1

Desktop Neo4j (3.4.7)
Fair warning: I'm very new to Cypher, Apoc, and Neo4J in general

I'm building a neo4j database for our service delivery, and as such I've already pulled in items like:
Employee, Customer, Contacts, and used apoc.load.jdbc to query and create some nodes/relationships. Those nodes have the relevant IDs so I can match them in further MATCH/MERGE commands.

Now I'm trying to do something a bit more complex, and having some trouble working this out. I want to import our tickets, and at the same time create relationships to the nodes (that already exist) for contact, employee, and company. I KNOW a ticket will be tied to a manager (employee) and MOST tickets will also have a contact and a company associated, but it is possible they wont.

So I wanted to create the ticket, create a [:MANAGED_BY] relationship to the employee who holds the ticket, and then if available match to the company (and make a relationship) and same for the customer contact. I assumed I would use WITH to perform additional matches, and this query runs without error, but it only creates the (Ticket) node (and the t.Manager and that relationship), but fails to add the t.Account, t.Contact or either of those relationships. Is there a better approach I should be taking here? Thanks for reading.

PS - I'm not sure if this post belongs in the APOC or the CYPHER category... what is proper etiquette regarding cross-posting for these forums?

call apoc.load.jdbc('jdbc:sqlserver://;servername=mysqlserver;databaseName=CommitCache;user=neo4jreader;password=somepassword',' SELECT TICKETNO,CARDID,CONTACTID,WORKERID,STATUS,SOURCE,ASSETRECID,DUEDATETIME from [CommitCache].[dbo].[Tickets]')
yield row
MATCH (e:Crmemployee {acctrecid:row.WORKERID})
MERGE (t:Ticket {TicketNumber:row.TICKETNO})
ON CREATE
set t.Manager = e.name
MERGE (t)-[:MANAGED_BY]->(e)
WITH t,row
MATCH (a:Company {acctrecid:row.CARDID}),(t)
set t.Account = a.name
MERGE (t)-[:TICKET_CLIENT]->(a)
with t,row
MATCH (c:Contact {acctrecid:row.CONTACTID}),(t)
set t.Contact=c.name
MERGE (t)-[:OPENED_FOR]->(c)
Return t,c

(Paul Drangeid) #2

I think my MATCH statements were wrong. since I am performing a WITH t, the match isn't necessary. (not sure why it failed to match, and yet provided no error)

Removing the ,(t) from the MATCH statement provided the results I was expecting...


(Paul Drangeid) #3

Another follow-up to my example. Because I was trying to match each type (even if a previous connection was null. I had to create some placeholders for "null" values, and perform either an OPTIONAL MATCH with coalesce (if I needed to lookup a value in a MATCH node) or just a coalesce, otherwise each JDBC row would quit at the first non-match it found.

CREATE (nomgr:Crmemployee {name:'None Provided',acctrecid:'None Provided'})
CREATE (nocmp:Company {name:'None Provided',acctrecid:'None Provided'})
CREATE (nocon:Contact {name:'None Provided',acctrecid:'None Provided'})
CREATE (nosrc:TicketSource {name:'None Provided'})
CREATE (notyp:TicketType {name:'None Provided'})
CREATE (nostat:TicketStatus {name:'None Provided'})

call apoc.load.jdbc('blah blah blah<- my JDBC query')
yield row
MATCH (e:Crmemployee {acctrecid:row.WORKERID})
MERGE (t:Ticket {TicketNumber:row.TICKETNO})
ON CREATE
set t.Manager = e.name
Merge (t)-[:MANAGED_BY]->(e)
WITH t,row
OPTIONAL MATCH (a1:Company {acctrecid:row.CARDID})
OPTIONAL MATCH (a:Company {acctrecid:coalesce(a1.acctrecid,'None Provided')})
set t.Account = a.name
MERGE (t)-[:TICKET_OF_CLIENT]->(a)
WITH t,row
OPTIONAL MATCH (c1:Contact {acctrecid:row.CONTACTID})
OPTIONAL MATCH (c:Contact {acctrecid:coalesce(c1.acctrecid,'None Provided')})
set t.Contact = coalesce(c.name,'None Provided')
MERGE (t)-[:TICKET_FOR]->(c)
with t,row
MATCH (src:TicketSource {name:coalesce(row.SOURCE,'None Provided')})
set t.Source = src.name
MERGE (t)-[:TICKET_SOURCE]->(src)
with t,row
MATCH (type:TicketType {name:coalesce(row.KIND,'None Provided')})
set t.Type = type.name
MERGE (t)-[:TICKET_TYPE]->(type)
With t,row
MATCH (stat:TicketStatus {statcode:coalesce(row.STATUS,'None Provided')})
set t.Status = stat.name
MERGE (t)-[:TICKET_STATUS]->(stat)