Help needed with cypher query - combining

[accounts4]

Hi,

i have a trade network between the world countries with the various product codes represented as a relationship property. Furthermore, there are several node properties as well.

My goal is to combine two cypher queries into one, where the new property will be set for the EU countries (nodes with the EU property) which are trading one specific product.

What i got until now:
1.) Defining node property (all countries which trade this specific good are included)

MATCH (c1:Country)-[r:EXPORTED]->(c2:Country) WHERE r.ProductHSCode="300220"
SET c1.Product300220='YES', c2.Product300220='YES'
RETURN count(c1)

2.) Virtualizing graph

CALL gds.graph.create.cypher(
'graf',
'MATCH (c:Country) WHERE c.Product300220="YES" RETURN id(c) AS id, labels(c) AS labels',
'MATCH (c1:Country)-[r:EXPORTED]->(c2:Country) WHERE r.ProductHSCode="300220" RETURN id(c1) AS source, id(c2) AS target, r.ProductQuantity AS ProductQuantity, r.ProductValue AS ProductValue, type(r) AS type')

3.) Setting the property for the EU countries

MATCH (c:Country)
WHERE c.CountryISO3Letter IN ['AUT','BGR','HRV','CYP','CZE','DNK','EST','FIN','FRA','GRC','HUN','IRL','ITA','LVA','LTU','LUX','MLT','NLD','POL','PRT','ROU','SVK','SVN','ESP','SWE']
SET c.EU='YES'

4.) I need a query which will satisfy two conditions - product code = "300220" (relationship property) and every EU country (node property).

This doesn't work:

CALL gds.graph.create.cypher(
'grafEU',
'MATCH (c:Country) WHERE c.Product300220="YES" AND c.EU="YES" RETURN id(c) AS id, labels(c) AS labels',
'MATCH (c1:Country)-[r:EXPORTED]->(c2:Country) WHERE r.ProductHSCode="300220" RETURN id(c1) AS source, id(c2) AS target, r.ProductQuantity AS ProductQuantity, r.ProductValue AS ProductValue, type(r) AS type')

because:

Failed to invoke procedure gds.graph.create.cypher: Caused by: java.lang.IllegalArgumentException: Failed to load a relationship because its source-node with id 237 is not part of the node query or projection. To ignore the relationship, set the configuration parameter validateRelationships to false.

[Bennu · Accepted solution]

Hi @accounts4 !

Looks like you are over-modeling (not sure if that's a thing). You should notice that your conditions are not the same between your Node and Relationships conditions.

I think this will work:

CALL gds.graph.create.cypher(
 'grafEU',
 'MATCH (c:Country) 
 WHERE c.Product300220="YES" 
 AND c.EU="YES" 
 RETURN id(c) AS id, labels(c) AS labels' ,
 'MATCH (c1:Country)-[r:EXPORTED]->(c2:Country) 
  WHERE r.ProductHSCode="300220" 
  and c1.EU = "YES"
  and c2.EU = "YES"
  RETURN id(c1) AS source, id(c2) AS target, r.ProductQuantity AS ProductQuantity, 
  r.ProductValue AS ProductValue, type(r) AS type'
)

You may like considering EU as a label. Also Product300220 may not be necessary based on ProductHSCode.

Bennu

[accounts4]

It is working!

You should notice that your conditions are not the same between your Node and Relationships conditions.

You are right, in my attempt i defined EU property only for the node conditions, but forgot about relationships conditions.

You may like considering EU as a label

I have only one label - Country. How do you think i would benefit from that?

Also Product300220 may not be necessary based on ProductHSCode.

What is the alternative then?

[Bennu]

Hi @accounts4 !

About the label on EU, it's all about positive conditions in order to have better performance long term. There's this article that talks about.

Instead of having a boolean (as a string) property for Europe, you can have an enumerator Region that goes 0 for Europe, 1 North America and so on. This will optimize disk space of the DB and it will keep you flexible to new features. Another option is creating directly a Label per region. So you can easily access this info. It's all about playing with your most frequent/important queries.

About the property Product300220, a country with this property set to YES it's a country that has/produce this element? Why don't you use a :Product node that has code = 300220 plus a relation beetween this element and the countries that produce him?

Bennu

[accounts4]

Hi @Bennu

About the label on EU, it's all about positive conditions in order to have better performance long term. There's this article that talks about.

Yes, you are right, but my DB is for one time use only so i indexed the nodes (labeled "Country") for one specific property and that's it. Otherwise, i can agree with you, but as i have stated, this is fixed data set which will remains as is in the future as well. Nevertheless, thanks for the tip!

About the property Product300220 , a country with this property set to YES it's a country that has/produce this element? Why don't you use a :Product node that has code = 300220 plus a relation between this element and the countries that produce him?

No, I'm dealing here with the relationships, ie. one way directed links which means that the country x exported the mentioned good to country y. With this label i wanted to take into consideration all the countries which exported Product300220 or the mentioned product was imported into certain country. Hence the following (can you please verify this?):

MATCH (c1:Country)-[r:EXPORTED]->(c2:Country) WHERE r.ProductHSCode="300220"
SET c1.Product300220='YES', c2.Product300220='YES'
RETURN count(c1)

&

CALL gds.graph.create.cypher(
'graf',
'MATCH (c:Country) WHERE c.Product300220="YES" RETURN id(c) AS id, labels(c) AS labels',
'MATCH (c1:Country)-[r:EXPORTED]->(c2:Country) WHERE r.ProductHSCode="300220" RETURN id(c1) AS source, id(c2) AS target, r.ProductQuantity AS ProductQuantity, r.ProductValue AS ProductValue, type(r) AS type')

[accounts4]

@Bennu - i forgot to mention you...

[accounts4]

@Bennu btw. is it possible to translate certain relationship to a new relationship type?

For example, i have only one relationship type (EXPORTED), but would it be possible to translate certain condition into a new relationship type?

MATCH (c1:Country)-[r:EXPORTED]->(c2:Country) WHERE r.ProductHSCode="300220"

Furthermore, as you have suggested, what would be the syntax to translate certain node condition into a new node label type? For example, i have existing node label type (Country), but would like to derivative EU countries from the existing label type?