CODEWITHSUNDEEP
neo4jcypher
Adrian
Adrian

Reputation: 1

Best way to combine Neo4j cypher queries (alternative to UNION)?

Given Model

There is a model with categories and relations between them. For relations, it can be specified, if they are bound to specific start or end categories.

The are four types of relations:

Code:

MERGE (cat:ModelCategory {title:'Cat'})
MERGE (rel1:ModelRelation {title:'Outgoing'})
MERGE (rel2:ModelRelation {title:'Incoming'})
MERGE (rel3:ModelRelation {title:'Outgoing and Incoming'})
MERGE (rel4:ModelRelation {title:'Unbound'})
MERGE (rel1)-[:STARTS_AT]->(cat)
MERGE (rel2)-[:ENDS_AT]->(cat)
MERGE (rel3)-[:STARTS_AT]->(cat)
MERGE (rel3)-[:ENDS_AT]->(cat)

Neo4j Browser Screenshot

Single queries

If you choose "Cat" as a start node and want to know, which relations and resulting end nodes can be created, you can use single queries:

// Relations with current source and a target
// Returns relation "Outgoing and Incoming"
MATCH (relation:ModelRelation)-[STARTS_AT]->(:ModelCategory{title:"Cat"}),
(relation)-[ENDS_AT]->(target)
RETURN DISTINCT relation, target

// Relations with current source and without target
// Returns relation "Outgoing"
MATCH (relation:ModelRelation)-[STARTS_AT]->(:ModelCategory{title:"Cat"})
WHERE NOT (relation)-[:ENDS_AT]->()
MATCH (allCategories:ModelCategory)
RETURN relation, allCategories as target

// Relations with target, without source
// Returns relation "Incoming"
MATCH (relation:ModelRelation)-[ENDS_AT]->(target)
WHERE NOT (relation)-[:STARTS_AT]->()
RETURN relation, target

// Relations without source or target
// Returns relation "Unbound"
MATCH (relation:ModelRelation)
WHERE NOT (relation)-[:STARTS_AT]->() AND NOT (relation)-[:ENDS_AT]->()
MATCH (allCategories:ModelCategory)
RETURN relation, allCategories as target

Question

What is the best way to combine the four queries?
The most simple solution is to add UNION between the statements.
Maybe it's a good idea to get all nodes of the labels ModelRelation and ModelCategory first and execute further queries on sub-graphs?

Update

A better solution is to disginguish between relations with or without specified targets. (Resulting in one category or all categories.) One UNION is still necessary and the first part of both subqueries is the same.

// Relations which start at selected category or have no specified start
MATCH (relation:ModelRelation)
WHERE (relation)-[:STARTS_AT]->(:ModelCategory{title:"Cat"}) OR
      NOT (relation)-[:STARTS_AT]->()

// Relations with specified targets
Match (relation)-[ENDS_AT]->(target) 
RETURN relation, target

UNION

// Relations which start at selected category or have no specified start
MATCH (relation:ModelRelation)
WHERE (relation)-[:STARTS_AT]->(:ModelCategory{title:"Cat"}) OR
      NOT (relation)-[:STARTS_AT]->()

// Relations without specified targets
MATCH (relation)
WHERE NOT (relation)-[:ENDS_AT]->()
MATCH (allCategories:ModelCategory)
RETURN relation, allCategories as target

Upvotes: 0

Views: 967

Answers (1)

Tore Eschliman
Tore Eschliman

Reputation: 2507

Use filter notation and variable-length paths to catch all of this in one query, then use CASE statements to replace null values with allCategories.

MATCH (m:ModelCategory)
WITH COLLECT(m) AS allCategories
MATCH path = (:ModelCategory) <- [:STARTS_AT*0..1] - (:ModelRelation) - [:ENDS_AT*0..1] -> (:ModelCategory)
WITH CASE WHEN ANY(x in RELATIONSHIPS(path) WHERE TYPE(x) = 'STARTS_AT') THEN NODES(path)[0] ELSE allCategories END AS start,
[x IN NODES(path) WHERE x:ModelRelation][0] as relation,
CASE WHEN ANY(x IN RELATIONSHIPS(path) WHERE TYPE(x) = 'ENDS_AT') THEN LAST(NODES(path)) ELSE allCategories END AS end
RETURN start, relation, end

Generally you can always replace UNION queries with the right combination of COLLECT, CASE, and either OPTIONAL MATCH or 0-length paths. It's worth it to go through the contortions, because you can then run aggregations on the results, instead of just returning the same column layout in every query.

EDIT: A simpler version, that returns a single row per relation.

MATCH (m:ModelCategory)
WITH COLLECT(m) AS allCategories
MATCH (relation:ModelRelation)
OPTIONAL MATCH (relation) - [:STARTS_AT] -> (start:ModelCategory)
OPTIONAL MATCH (relation) - [:ENDS_AT] -> (end:ModelCategory)
WITH COLLECT(start) AS starts, relation, COLLECT(end) AS ends, allCategories
RETURN
CASE starts WHEN [] THEN allCategories ELSE starts END AS relationStarts,
relation,
CASE ends WHEN [] THEN allCategories ELSE ends END AS relationEnds

Upvotes: 1

Related Questions