OPTIONAL MATCH (organization:Organization{uuid:{organizationId}})-[orgCandidateRel:HAS]->(candidate:Candidate{uuid:{candidateId}}) WITH * "+
"OPTIONAL MATCH (candidate)-[orgOwnRel:OWNER]->(owner:Employee) WITH * "+
"OPTIONAL MATCH (candidate)-[orgSourceRel:SOURCED_BY]->(sourcedBy:Employee) WITH * "+
"OPTIONAL MATCH (candidate)-[candidateSkill:HAS]->(skill:Skill) WITH * "+
"OPTIONAL MATCH (candidate)-[candidateProject:HAS]->(project:Project) WITH * "+
"OPTIONAL MATCH (candidate)-[candidateRecommendation:HAS]->(recommendation:Recommendation) WITH * "+
"OPTIONAL MATCH (candidate)-[candidateAccomplishment:HAS]->(accomplishment:Accomplishment) WITH * "+
"OPTIONAL MATCH (candidate)-[candidateCertification:HAS]->(certification:Certification) WITH * "+
"OPTIONAL MATCH (candidate)-[candidateEducation:HAS]->(education1:Education) WITH * "+
"OPTIONAL MATCH (candidate)-[candidateEmployment:HAS]->(position:Position) WITH * "+
"OPTIONAL MATCH (candidate)-[candidateInterviewRel:IS_IN{isCurrentStage:true}]->(currInterview:Interview)<-[jobInterviewRel:HAS]-(job:Job) WITH * "+
"OPTIONAL MATCH (job)-[canJobRel:HAS]->(candidate) "+
"OPTIONAL MATCH (candidate)-[:REASON{jobId:job.uuid}]->(rejectionReason:Reason{reasonType:'REJECTION'})<-[:REASON]-(job) "+
"OPTIONAL MATCH (job)-[:HAS]->(interview:Interview) WITH organization, job, canJobRel, rejectionReason, owner, orgCandidateRel, candidate, sourcedBy, interview,skill, project, recommendation, certification, accomplishment, position, education1, "+
"CASE WHEN currInterview.uuid = interview.uuid THEN true ELSE false END AS stageStatus WITH organization, job, canJobRel, rejectionReason, owner, orgCandidateRel, candidate, sourcedBy, COLLECT(interview{., currStage:stageStatus}) AS interviews ,skill, project, recommendation, certification, accomplishment, position, education1 "+
"OPTIONAL MATCH (skill)-[skillDocRel:HAS]-(skillDocument:Document) WITH organization, job, canJobRel, rejectionReason, owner, orgCandidateRel, candidate, sourcedBy, interviews, COLLECT(skillDocument{.}) AS skillDocuments ,skill, project, recommendation, certification, accomplishment, position, education1 "+
"OPTIONAL MATCH (project)-[projectDocRel:HAS]-(projectDocument:Document) WITH organization, job, canJobRel, rejectionReason, owner, orgCandidateRel, candidate, sourcedBy, interviews, skillDocuments, COLLECT(projectDocument{.}) AS projectDocuments ,skill, project, recommendation, certification, accomplishment, position, education1 "+
"OPTIONAL MATCH (recommendation)-[recommendationDocRel:HAS]-(recommendationDocument:Document) WITH organization, job, canJobRel, rejectionReason, owner, orgCandidateRel, candidate, sourcedBy, interviews, skillDocuments, projectDocuments, COLLECT(recommendationDocument{.}) AS recommendationDocuments ,skill, project, recommendation, certification, accomplishment, position, education1 "+
"OPTIONAL MATCH (accomplishment)-[accomplishmentDocRel:HAS]-(accomplishmentDocument:Document) WITH organization, job, canJobRel, rejectionReason, owner, orgCandidateRel, candidate, sourcedBy, interviews, skillDocuments, projectDocuments, recommendationDocuments, COLLECT(accomplishmentDocument{.}) AS accomplishmentDocuments ,skill, project, recommendation, certification, accomplishment, position, education1 "+
"OPTIONAL MATCH (certification)-[certificationDocRel:HAS]-(certificationDocument:Document) WITH organization, job, canJobRel, rejectionReason, owner, orgCandidateRel, candidate, sourcedBy, interviews, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, COLLECT(certificationDocument{.}) AS certificationDocuments ,skill, project, recommendation, certification, accomplishment, position, education1 "+
"OPTIONAL MATCH (education1)-[education1DocRel:HAS]-(education1Document:Document) WITH organization, job, canJobRel, rejectionReason, owner, orgCandidateRel, candidate, sourcedBy, interviews, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, COLLECT(education1Document{.}) AS education1Documents ,skill, project, recommendation, certification, accomplishment, position, education1 "+
"OPTIONAL MATCH (position)-[positionDocRel:HAS]-(positionDocument:Document) WITH organization, job, canJobRel, rejectionReason, owner, orgCandidateRel, candidate, sourcedBy, interviews, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, education1Documents, COLLECT(positionDocument{.}) AS positionDocuments ,skill, project, recommendation, certification, accomplishment, position, education1 "+
"OPTIONAL MATCH (candidate)-[candidateTagRel:HAS]->(tag:Tag) WITH organization, interviews, job, canJobRel, rejectionReason, owner, candidate, sourcedBy, orgCandidateRel, COLLECT(tag{.}) AS tags, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, education1Documents, positionDocuments,skill, project, recommendation, certification, accomplishment, position, education1 "+
"OPTIONAL MATCH (candidate)-[candidateDocumentRel:HAS]->(document:Document) WITH organization, owner, job, canJobRel, rejectionReason, candidate, sourcedBy, interviews, tags, orgCandidateRel, COLLECT(document{.}) AS documents, skill, project, recommendation, certification, accomplishment, position, education1, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, education1Documents, positionDocuments "+
"OPTIONAL MATCH (candidate)-[candidateFieldRel:FIELDS]->(customField:CustomField) WITH organization, owner, job, canJobRel, rejectionReason, interviews, tags, candidate, sourcedBy, documents, orgCandidateRel, COLLECT(customField) AS customFields, skill, project, recommendation, certification, accomplishment, position, education1, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, education1Documents, positionDocuments, "+
"CASE WHEN job IS NOT NULL THEN apoc.map.setValues(job, ['interviews', interviews, 'rejectionStatus', canJobRel.rejectionStatus, 'rejectionReason', rejectionReason{.}]) ELSE NULL END as jobInterview with Collect(DISTINCT jobInterview) as jobs, owner, organization, tags, candidate, sourcedBy, documents, orgCandidateRel, customFields,skill, project, recommendation, certification, accomplishment, position, education1,skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, education1Documents, positionDocuments, "+
"CASE WHEN skill IS NOT NULL THEN apoc.map.setValues(skill, ['documents', skillDocuments]) ELSE NULL END as skillDocument with Collect(skillDocument) as skills, owner, organization, tags, candidate, sourcedBy, documents, orgCandidateRel, customFields, jobs,skill, project, recommendation, certification, accomplishment, position, education1, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, education1Documents, positionDocuments, "+
"CASE WHEN project IS NOT NULL THEN apoc.map.setValues(project, ['documents', projectDocuments]) ELSE NULL END as projectDocument with Collect(projectDocument) as projects, owner, organization, tags, candidate, sourcedBy, documents, orgCandidateRel, customFields, jobs, skills ,skill, project, recommendation, certification, accomplishment, position, education1, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, education1Documents, positionDocuments, "+
"CASE WHEN accomplishment IS NOT NULL THEN apoc.map.setValues(accomplishment, ['documents', accomplishmentDocuments]) ELSE NULL END as accomplishmentDocument with Collect(accomplishmentDocument) as accomplishments, owner, organization, tags, candidate, sourcedBy, documents, orgCandidateRel, customFields, jobs, skills, projects,skill, project, recommendation, certification, accomplishment, position, education1, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, education1Documents, positionDocuments, "+
"CASE WHEN certification IS NOT NULL THEN apoc.map.setValues(certification, ['documents', certificationDocuments]) ELSE NULL END as certificationDocument with Collect(certificationDocument) as certifications, owner, organization, tags, candidate, sourcedBy, documents, orgCandidateRel, customFields, jobs, skills, projects, accomplishments,skill, project, recommendation, certification, accomplishment, position, education1, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, education1Documents, positionDocuments, "+
"CASE WHEN education1 IS NOT NULL THEN apoc.map.setValues(skill, ['documents', education1Documents]) ELSE NULL END as education1Document with Collect(education1) as education, owner, organization, tags, candidate, sourcedBy, documents, orgCandidateRel, customFields, jobs, skills, projects, accomplishments,certifications,skill, project, recommendation, certification, accomplishment, position, education1, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, education1Documents, positionDocuments, "+
"CASE WHEN position IS NOT NULL THEN apoc.map.setValues(skill, ['documents', positionDocuments]) ELSE NULL END as positionDocument with Collect(positionDocument) as positions, owner, organization, tags, candidate, sourcedBy, documents, orgCandidateRel, customFields, jobs, skills, projects, accomplishments, certifications, education, skill, project, recommendation, certification, accomplishment, position, education1, skillDocuments, projectDocuments, recommendationDocuments, accomplishmentDocuments, certificationDocuments, education1Documents, positionDocuments, "+
"CASE WHEN recommendation IS NOT NULL THEN apoc.map.setValues(recommendation, ['documents', recommendationDocuments]) ELSE NULL END as recommendationDocument with Collect(recommendationDocument) as recommendations, owner, organization, tags, candidate, sourcedBy, documents, orgCandidateRel, customFields, jobs, skills, projects, accomplishments, education, certifications, positions "+
"OPTIONAL MATCH (follower:Employee)-[:FOLLOWS]->(candidate) WITH COLLECT(follower{.}) AS followers, recommendations, owner, organization, tags, candidate, sourcedBy, documents, orgCandidateRel, customFields, jobs, skills, projects, accomplishments, education, certifications, positions "+
"OPTIONAL MATCH (candidate)-[:SOURCE]->(source:Source) WITH followers, recommendations, owner, organization, tags, candidate, sourcedBy, documents, orgCandidateRel, customFields, jobs, skills, projects, accomplishments, education, certifications, positions, source, candidate.candidateId as candidateId "+
"OPTIONAL MATCH (c1:Candidate{candidateId:candidateId}) WITH COLLECT(c1.uuid) AS duplicateIds, followers, recommendations, owner, organization, tags, candidate, sourcedBy, documents, orgCandidateRel, customFields, jobs, skills, projects, accomplishments, education, certifications, positions, source "+
"RETURN owner AS owner, customFields as fields, sourcedBy, documents, tags, jobs, candidate, organization, skills, projects, accomplishments, recommendations, certifications, positions, education, orgCandidateRel IS NULL AS isOrgCandidateRelNull, followers, source, duplicateIds ")
You may want to capture a PROFILE plan of this query, watch how the number of rows increases across operations.
Usually, to get these down, if you are planning on collecting items, collect immediately after the MATCH (or use pattern comprehensions instead, they're usually more efficient) and pay attention to matches that can generate more than one row of results where you aren't collecting.
Remember that operations in Cypher produce rows, and they execute per row, so the more rows you end up generating, the more work all subsequent operations have to do, so keeping the rows down, using aggregations to group results as soon as it makes sense, is useful to keep this under control.
1 Like