CTE Queries

[Ryan John Velasco]

How do you do CTE queries in neo4j?

SAMPLE QUERY:

MATCH (rootTopic:Company)-[:Seller|Buyer]->(:SalesRelation) WHERE rootTopic.ID = 172096

MATCH (sellerCompanyTopic:Company)-[rel:Seller]->(sr:SalesRelation)-[rel2:Buyer]-(buyer:Company), 

sr<-[:PriceAgreement]-(contract:Contract)

OPTIONAL MATCH p = shortestPath(rootTopic-[:Seller|Buyer*]-sellerCompanyTopic)

WITH DISTINCT rootTopic, sellerCompanyTopic, buyer, contract,  

reduce(

index = 0, 

type IN EXTRACT(r in relationships(p) | type(r))| 

case 

when index % 2 = 0 AND type = 'Buyer'

then -1000

when index % 2 = 1 AND type = 'Seller'

then -1000

ELSE index + 1

END

) AS Related

WHERE rootTopic = sellerCompanyTopic OR rootTopic = buyer OR Related > 0

RETURN rootTopic.ID AS RootCompanyTopicID, sellerCompanyTopic.ID AS SellerTopicID, buyer.ID AS BuyerTopicID, contract.ID AS ContractTopicID, case 

when Related is null

then 1

when Related < 0

then 0

ELSE Related

END AS Depth

ORDER BY RootCompanyTopicID, SellerTopicID, BuyerTopicID, ContractTopicID

Best Regards,

Ryan

[Michael Hunger]

This query spans up a lot of paths, did you run it with PROFILE on your dataset?

Some suggestions:

* you don't do anything with the first :SalesRelation is that intentional? if yes then move it to a where clause so it doesn't span up paths

* your second match is NOT bound at all? is it is potentially gigantous

* how many  (sellerCompanyTopic:Company) are there?

* and how many of these:

MATCH (sellerCompanyTopic:Company)-[rel:Seller]->(sr:SalesRelation)-[rel2:Buyer]-(buyer:Company), 

sr<-[:PriceAgreement]-(contract:Contract)

* perhaps you can reformulate your related somehow

* an alternative would also be to do a UNION instead two of which would have rootTopic being the same as sellerCompanyTopic:Company or in the other case to buyer and only the third would have to look at related

Any chance to get the database?

Am 05.10.2015 um 07:02 schrieb Ryan John Velasco <ryanjohn...@gmail.com>:

How do you do CTE queries in neo4j?

SAMPLE QUERY:

MATCH (rootTopic:Company) WHERE rootTopic.ID = 172096 AND (rootTopic)-[:Seller|Buyer]->(:SalesRelation)

--
You received this message because you are subscribed to the Google Groups "Neo4j" group.
To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ryan Velasco]

Thanks for the reply. Actually that query results nearly correct with sql server counterpart. But there is an issue with shortest path where in I cannot dictate that it should be seller, buyer, seller, buyer....

The new query right now which is nearly resulting to the same result as our sql server view is:

 

MATCH (sellerCompanyTopic:Company)-[rel:Seller]->(sr:SalesRelation)-[rel2:Buyer]-(buyer:Company), 

sr<-[pa:PriceAgreement]-(contract:Contract)

WHERE ((contract.ContractEndDate >= 635796943890000000) AND (contract.ContractStartDate <= 635796943890000000)

 AND (pa.EndDate >= 635796951430000000) AND (pa.StartDate <= 635796951430000000))

MATCH (rootTopic:Company) WHERE rootTopic.ID = 172086

OPTIONAL MATCH p = allshortestPaths(rootTopic-[:Seller|Buyer*]-sellerCompanyTopic)

WITH rootTopic, sellerCompanyTopic, buyer, contract,  

reduce(

index = 0, 

rel IN relationships(p)| 

case 

when index % 2 = 0 AND type(rel) = 'Buyer'

then -1000

when index % 2 = 1 AND type(rel) = 'Seller'

then -1000

ELSE index + 1

END

) AS Related

WHERE rootTopic = sellerCompanyTopic OR rootTopic = buyer OR Related > 0

RETURN distinct buyer.ID AS BuyerTopicID, contract.ID AS ContractTopicID, case 

when Related is null

then 1

when Related < 0

then 0

when rootTopic = buyer

then 0

ELSE Related

END AS Depth

ORDER BY BuyerTopicID, ContractTopicID

The problem is the allshortestpaths because  I cannot dictate that it should be seller, buyer, seller, buyer....
Which become a problem for this scenario : 

Company1

  |

  | Sells to (via contract A)

  |

Company2

    

Company2

  |

  | Sells to (via Contract B)

  |

Company3

Company3

  |

  | Sells to (via Contract C)

  |

Company4

Company4

  |

  | Sells to (via Contract D)

  |

Company1

For Checking if Company 1 Seller Buyer Chain with Company 4,

the problem is that the shortest path is always Contract D and the correct path should be  A + B + C path.

[Michael Hunger]

How does it behave if you leave off the shortest path and instead use a normal path?

What is the usual distance between those 2 topics?

[Ryan Velasco]

t becomes very slow or it never ends.

If I should be doing gremlin, upon learning gremlin, the query should be like this(Hopefully this is correct. Hehehe! I just learned it for 2 hours now) .

g.v(172086).as('seller').outE('Seller').inV('SalesRelation').outE('Buyer').inV('Company').as('buyer').loop('seller'){it.outE('Seller').count() > 0}.table(result, ['seller','buyer'])

UNION

g.v(172086).as('rootTopic').outE('Buyer').inV('SalesRelation').outE('Seller').inV('Company').as('seller').table(result, ['rootTopic','seller'])

[Michael Hunger]

Does this mean that you have at least ONE -[Seller]->[Buyer]-> pair?

Did you try:

MATCH p = (rootTopic)-[:Seller|Buyer*]->(sellerCompanyTopic)

WHERE ALL(idx in range(0,length(p)-2,2) WHERE type(rels(p)[idx]) = "Seller" AND type(rels(p)[idx+1]) = "Buyer")

or

MATCH p = (rootTopic)-[:Seller]->()-[:Buyer]->()-[:Seller|Buyer*0..]->(sellerCompanyTopic)

WHERE ALL(idx in range(2,length(p)-2) WHERE type(rels(p)[idx]) = type(rels(p)[idx-2]))

Am 06.10.2015 um 11:34 schrieb Ryan Velasco <ry...@limesource.se>:

[Ryan Velasco]

I tried it but it is taking so much time. It is because the path   -[:Seller|Buyer*0..]-(sellerCompanyTopic) make it very slow.

Does this mean that you have at least ONE -[Seller]->[Buyer]-> pair?

It can also be that there is not seller buyer pair.

SAMPLE output should be:

 Seller (child of tree are buyer of 'Seller')

Buyer (child of tree are buyer of 'Buyer' as seller in the relationship)

Buyer^1 (child of tree are buyer of 'Buyer^1' as seller in the relationship)

Buyer^2

Buyer^1

Buyer^1

Buyer^2(child of tree are buyer of 'Buyer^2' as seller in the relationship)

Buyer^3

Buyer

Buyer^1

Buyer^1

Buyer^1

Buyer^3

Please see SalesRelationModel.png

You can see on that image that there is a node SalesRelation with a seller, a buyer company, a contract and etc. So the SaleRelation is the hub that connect all information necessary for a sale to exist.

The buyer company is also a seller to other smaller company with a different SalesRelation but are connected(because we are referring to the same buyer) and so on...

[Michael Hunger]

But if you have a direction then the expansion should be really limited and narrow.

I "think" it might be because the rest of the query spans out too many rows.

But without a database to test it, this is only in vain.

Michael

--
You received this message because you are subscribed to the Google Groups "Neo4j" group.
To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

<SalesRelation.json><SalesRelationModel.png>

[Ryan Velasco]

 Database is around 27MB... Let me send it you in skype. The expansion of tree is dynamic so the depth is unknown.

[Michael Hunger]

ok, thx.

Michael

Am 07.10.2015 um 12:13 schrieb Ryan Velasco <ry...@limesource.se>:

 Database is around 27MB... Let me send it you in skype. The expansion of tree is dynamic so the depth is unknown.

[Ryan Velasco]

Was able to make it work in gremlin using this query.

x = [] ;g.v(172079).as('seller').store(x).outE('Seller').inV.inE('Buyer').outV.as('buyer').loop('seller'){x.contains(it.object) == false}

x will contain the initial vertex which is the root seller and seller-buyer chain children.

[Ryan Velasco]

The gremlin that result the same with our sql server view:

x = [];t = new Table();g.v(172079).as('root').outE('Seller').inV.as('sr').store(x).inE('PriceAgreement').outV.as('contract').outE('PriceAgreement').inV.inE('Buyer').outV.as('buyer').table(t, ['contract','buyer']).outE('Seller').inV.loop('sr'){x.contains(it.object) == false}

r = new ArrayList(); t.each{r->y.add([Buyer:r[1].ID,  Contract:r[0].ID])}

I added you to skype. Please accept my invitation.

Rg,

Ryan

[Ryan Velasco]

Hello,

If neo4j will allow the following allshortestPaths(rootTopic-[:Seller]->()<-[:Buyer]-sellerCompanyTopic) that would be very useful.

Best Regards,

Ryan