Cypher to find (multiple) "lowest" nodes

Question

Case:

I've nodes Categories that looks like below and i created a relation from category to parent.

CREATE(p1:Categorie {
  id: 2,
  parent_id: 1,
  name: "Kids",
  is_active: true ,
  position: 1,
  level: 1,
})
CREATE(c1:Categorie {
  id: 5,
  parent_id: 2,
  name: "Toys",
  is_active: true ,
  position: 1,
  level: 2,
})

//New root category
CREATE(p2:Categorie {
  id: 9,
  parent_id: 1,
  name: "Holiday",
  is_active: true ,
  position: 1,
  level: 1,
})
CREATE(c2:Categorie {
  id: 12,
  parent_id: 9,
  name: "Water",
  is_active: true ,
  position: 1,
  level: 2,
})


CREATE(c1)-[:CHILD_OF]->(p1)
CREATE(c2)-[:CHILD_OF]->(p2)

Then I have products that hase an array of categorie_ids like this:

{
    "sku": "abc",
    "name": "Electric boot",
    "categorie_ids": [ 
        1,
        5,
        9,
        12
    ]
}

The API gives all the ids back that the product is "in". For me this so much clutter in all the relationships. This example is minimum but in reality this could be up to twenty relations per product.

Question/Problem:

I would like to create a relation to the lowest node(s) in the categories. In this case the product has two main categories and thus two bottom categories. So I would like to create a relation to id 12 and 5.

I just can't wrap my head around the cypher query. Can someone shine some light on this?

Answer 1

You're basically looking at the following query :

MATCH Categorie nodes where their id is in the given list of ids and those nodes shouldn't be the parent of any other Categorie

Based on your model, you're looking at adding a predicate that Categorie nodes do not have an INCOMING relationship of type CHILD_OF

WITH [1,5,9,12] AS categoryIds
MATCH (n:Categorie)
WHERE n.id IN categoryIds
AND NOT ()-[:CHILD_OF]->(n)
RETURN n.id