Get latest node with specific value of node property

I have a schema where Product contains many revisions ProductRevision which say in which status the product is and it is connected to specific ProductParts configuration with nodes for each Part. Each Part can be used in many ProductParts and each ProductParts is used usually in three revisions (status New, Tested and Production). If ProductParts is changed, a new revision is created with status New and connected with previons last revision.

I have there also shortcuts like relationships LATEST, LATEST_NEW, LATEST_TESTED and LATEST_PRODUCTION which connect Product directly with latest node of revision, latest node of revision in status New, etc.

Command for creating simple example is here

CREATE (p:Product {Name:"Test1"})-[:REVISION {Created:datetime()}]-> 
(pr1:ProductRevision {Status:"New", Created:datetime()})-[:USING]->(pp:ProductParts)
CREATE (pp)-[:CONTAINS]->(p1:Part {Id:1})
CREATE (pp)-[:CONTAINS]->(p2:Part {Id:2})
CREATE (pr1)-[:REVISION {Created:datetime()}]->(pr2:ProductRevision {Status:"Tested", Created:datetime()})-[:USING]->(pp)
CREATE (pr2)-[:REVISION {Created:datetime()}]->(pr3:ProductRevision {Status:"Production", Created:datetime()})-[:USING]->(pp)
CREATE (ppChanged:ProductParts)
CREATE (ppChanged)-[:USING]->(p1)
CREATE (ppChanged)-[:USING]->(p3:Part {Id:3})
CREATE (pr3)-[:REVISION {Created:datetime()}]->(pr4:ProductRevision {Status:"New", Created:datetime()})-[:USING]->(ppChanged)
CREATE (pr4)-[:REVISION {Created:datetime()}]->(pr5:ProductRevision {Status:"Tested", Created:datetime()})-[:USING]->(ppChanged)
CREATE (p)-[:LATEST {Created:datetime()}]->(pr5)
CREATE (p)-[:LATEST_NEW {Created:datetime()}]->(pr4)
CREATE (p)-[:LATEST_TESTED {Created:datetime()}]->(pr5)
CREATE (p)-[:LATEST_PRODUCTION {Created:datetime()}]->(pr3)

So and I need to get chain like (Product)-(ProductParts)-(Part) where ProductParts is connected through latest revision in specific status, e.g. Tested or Production.

I wanted to use shortcuts relationships LATEST_* but it doesn't work as I expected. I tried query like

MATCH (p:Product)-[:LATEST_TESTED|LATEST_PRODUCTION]-(pr:ProductRevision)--(pp:ProductParts)--(pa:Part)
WITH *, max(pr.Created) as prc
RETURN p,pr,prc,pp,pa

but it returns all ProductParts with specific status not only the last one.

If I don't return pr relation between Product and ProductParts missing.

Exists any way how to get following result when I want latest revision with specific status Tested or Production

(:Product {Name: "Test"}) --> (:ProductRevision {Status:"Tested"}) --> (:ProductParts) --> (:Part {Id:1})
.                                                                                      \-> (:Part {Id:3})

Solution

You have to split your request in two parts: - First, get the latest ProductRevision you are interested in - Second, get all ProductParts and Parts related to the ProductRevision

MATCH (p:Product)-[:LATEST_TESTED|LATEST_PRODUCTION]-(pr:ProductRevision)
WITH p, pr
ORDER BY pr.Created DESC
LIMIT 1
MATCH (pr)--(pp:ProductParts)--(pa:Part)
RETURN p, pr, pp, pa

The first MATCH gets the latest ProductRevision by ordering the results descending and keeping only the first result The second MATCH gets the rest of the information you need.