A case for NOT IN

[wolf_goe]

Hi All,

I have a situation where I get 2 different matches and I need to get one node from one of them that does not exist in the other.

For example:

create (a:Host {name:'aA'})
create (b:Host {name:'aB'})
create (c:Host {name:'aC'})

create (a1:Host {name:'aD'})
create (b1:Host {name:'aE'})
create (c1:Host {name:'aF'})


create (d:Cluster {name:'bA'})
create (d1:Cluster {name:'bB'})

create (e:Datastore {name:'cA'})
create (e1:Datastore {name:'cB'})
create 
(d)-[:CONTAINS]->(a),
(d)-[:CONTAINS]->(b),
(d)-[:CONTAINS]->(c),
(e)-[:MAPPED_TO]->(a),
(e)-[:MAPPED_TO]->(b),
(d1)-[:CONTAINS]->(a1),
(d1)-[:CONTAINS]->(b1),
(d1)-[:CONTAINS]->(c1),
(e1)-[:MAPPED_TO]->(b1);

Now:

match (n)-[r]-() return *;

Would return:

What I need is to only return those Hosts that, although contained in a Cluster, does not have that Datastore Mapped to it. So, from the group in the left, given the Datastore named 'cA' I would need only the Host named 'aC' and from the group in the right, given the Datastore named 'cB' I would need to get Hosts named 'aF' and 'aD'.

I tried something on the lines of creating a collect from a cypher that returns all Hosts that are Contained in a Cluster and then matching the Datastore with the Hosts for the same Cluster and using NOT IN, but that always returns the same 3 hosts. I guess I'm thinking more on the equivalent on SQLServer (EXCEPT) or Oracle (MINUS), and I don't seem to understand the concept on cypher.

The goal is, without passing a Datastore or a Cluster as parameter, return all the Datastores that are mapped to some, but not all, of the hosts that are Contained in a Cluster and those Hosts without the Datastore mapping.

Anyone has a clue?

Wolf

[koji]

Hi @wolf_goe

What I need is to only return those Hosts that, although contained in a Cluster, does not have that Datastore Mapped to it. So, from the group in the left, given the Datastore named 'cA' I would need only the Host named 'aC' and from the group in the right, given the Datastore named 'cB' I would need to get Hosts named 'aF' and 'aD'.

How about this code.

MATCH (e:Datastore {name: 'cB'})-[:MAPPED_TO]->(h:Host)<-[:CONTAINS]-(c:Cluster)
WITH collect(id(h)) AS hostids, id(c) AS clusterid
MATCH (c:Cluster)-[:CONTAINS]->(h:Host)
  WHERE id(c) = clusterid
  AND NOT id(h) IN hostids
RETURN h;

[anthapu]

You can use pattern exists or not exists here.

MATCH (e:Datastore {name: 'cB'})-[:MAPPED_TO]->(h)<-[:CONTAINS]-(c)
WITH DISTINCT c
MATCH (c)-[:CONTAINS]->(h)
WHERE NOT EXISTS((h)-[:MAPPED_TO]->())
RETURN h

Here all the hosts which are part of cluster but not associated to datastore are returned. If you want the paths

MATCH (e:Datastore {name: 'cB'})-[:MAPPED_TO]->(h)<-[:CONTAINS]-(c)
WITH DISTINCT c
MATCH path=(c)-[:CONTAINS]->(h)
WHERE NOT EXISTS((h)-[:MAPPED_TO]->())
RETURN path