CODEWITHSUNDEEP
neo4jcypher
norbjd
norbjd

Reputation: 11237

Cypher : Union between queries, with an initial node retrieval

I am trying to write a Cypher query which is kind of a "union" query, basically something like :

MATCH (myNodeUsedInAllSubQueries)
WITH myNodeUsedInAllSubQueries
MATCH (a)-[...]->(myNodeUsedInAllSubQueries)
RETURN a
UNION
MATCH (b)-[...]->(myNodeUsedInAllSubQueries)
RETURN b
...

I want to match (myNodeUsedInAllSubQueries) once at the beginning because this node is used in all subqueries. Of course, the retrieval of this node in my example is not just as simple as this (it involves relationships and a WHERE clause, but I'll keep it simple here).

I'll try to explain this with a simple example and what I've tried so far.

Example

Graphically, the graph structure is the following :

(John: Person) ----------[LIKES]----------> (pizza: Food)
                                               ^
        (Developers: Group) ------[LIKES]------┘ 

(Jim: Person) --[BELONGS_TO]--> (Business: Group) --[LIKES]--> (apple: Food)

The example is accessible here in Neo4J console. The import script is here (in case you want to reproduce the issue locally) :

CREATE (john: Person{name: "John"});
CREATE (jim: Person{name: "Jim"});

CREATE (devs: Group{name: "Developers"});
CREATE (business: Group{name: "Business"});

CREATE (pizza: Food{name: "pizza"});
CREATE (apple: Food{name: "apple"});

MATCH (john: Person{name: "John"}), (pizza: Food{name: "pizza"})
CREATE (john)-[:LIKES]->(pizza);

MATCH (jim: Person{name: "Jim"}), (business: Group{name: "Business"})
CREATE (jim)-[:BELONGS_TO]->(business);

MATCH (devs: Group{name: "Developers"}), (pizza: Food{name: "pizza"})
CREATE (devs)-[:LIKES]->(pizza);

MATCH (business: Group{name: "Business"}), (apple: Food{name: "apple"})
CREATE (business)-[:LIKES]->(apple);

For that particular example, my question is : how to retrieve all Person nodes who likes pizza? Knowing that :

I'd also like to retrieve the pizza node once. As I said in introduction, in my real example, the retrieval of pizza could be quite complicated, so I don't want to duplicate the MATCH in each case.

What I've tried

Using UNION

MATCH (pizza: Food{name: "pizza"})
WITH pizza
MATCH (p: Person)-[:LIKES]->(pizza)
RETURN p
UNION
MATCH (p: Person)-[:BELONGS_TO]->(g: Group)-[:LIKES]->(pizza)
RETURN p

This query returns surprinsingly 2 nodes : John and Jim. After investigating, I have understood that this is because (pizza) in the 2nd UNION clause does not refer to the pizza node returned by the first MATCH, but rather any node. We can confirm that by running :

MATCH (pizza: Food{name: "pizza"})
WITH pizza
MATCH (p: Person)-[:LIKES]->(pizza)
RETURN p
UNION
MATCH (p: Person)-[:BELONGS_TO]->(g: Group)-[:LIKES]->(anything)
WHERE anything = pizza
RETURN p

This returns an error :

Neo.ClientError.Statement.SyntaxError: Variable `pizza` not defined (line 7, column 18 (offset: 184))
"WHERE anything = pizza"

, validating the fact that pizza node is not propagated to the second MATCH.

Using WITH and COLLECT

This fixes the problem of the pizza node that cannot be passed from the first MATCH to the 3nd MATCH.

MATCH (pizza: Food{name: "pizza"})
WITH pizza
MATCH (p: Person)-[:LIKES]->(pizza)
WITH pizza, COLLECT(p) AS people
MATCH (p: Person)-[:BELONGS_TO]->(g: Group)-[:LIKES]->(pizza)
RETURN people + COLLECT(p) AS people

But this query does not return anything : this is because the 3nd MATCH does not return anything (there are no Person in a Group that LIKES pizza).

If I add a Person in the Developers Group :

MATCH (devs: Group{name: "Developers"})
CREATE (emma: Person{name: "Emma"})-[:BELONGS_TO]->(devs)

The previous query returns successfully John and Emma because the last MATCH returns at least one Person. But in my case, it is possible that this MATCH does not return anything, so that does not work.

I hope this example is simple and clear. In this case, the query I'm looking for should return only John, because he's the only Person here who likes pizza.

Upvotes: 1

Views: 1031

Answers (2)

cybersam
cybersam

Reputation: 66999

[UPDATED]

This should work for your simple use case:

MATCH (p:Person)-[:BELONGS_TO|LIKES*..2]->(food:Food {name:"pizza"})
RETURN food, COLLECT(p) AS people;

Upvotes: 1

InverseFalcon
InverseFalcon

Reputation: 30397

As an alternate to cybersam's answer, if your graph structure is such that MATCH (p:Person)-[:BELONGS_TO|LIKES*..2]->(food:Food) won't work (for example if you're working on a graph where people can like each other, and you don't want to return a person who likes another person who likes pizza, which would adhere to your pattern), you can use a 0..1 length relationship to represent an optional step in the pattern:

MATCH (p:Person)-[:BELONGS_TO*0..1]->()-[:LIKES]->(food:Food)
WHERE food.name = "pizza"
RETURN COLLECT(p) AS people;

This allows matching to both of these patterns at the same time:

(p:Person)-[:LIKES]->(food:Food)

and

(p:Person)-[:BELONGS_TO]->()-[:LIKES]->(food:Food)

I've written up a knowledge base article on this approach.

Upvotes: 1

Related Questions