depth in nodes of the same label in custom query

Question

I have in my database Company with:

@Relationship(type = "COLLECTS_FROM")
private Set<Company> subCompanies

What I'm trying to achieve is to create two queries:

• get the company with limited depth 2 for nodes of the same type (so company which collects from different companies, which collects from different companies)
• get the company with full depth for nodes of the same type (similar to previous, but just all the way down without any limitations.

I had a few attempts to solve this and here are those:

• first approach was to use findById(ID,2) and findById(ID,999999), but this query has a huge performance drop and I ran into StackOverflow exception, that's why I'm trying to create my own methods similar to what I found here.

• second approach was to use custom query (which were actually working in case of different node types):

    @Query("Match (company:Company {name:$name})-[relation:COLLECTS_FROM*0..2]->(secondCompany:Company) RETURN company,relation,secondCompany")
    Company customerWithCustomDepth(String name);

Unfortunately while using the same node types I ran into the IncorrectResultSizeDataAccessException: Incorrect result size: expected at most 1 and when I changed return type to the list 3 records were returned (actually the first one of them is what I would like to get, but it seems like specifying secondCompany in the return statement populates nodes that I don't want to have in my return type.

• on the third approach I did similar to the second, but it also returned 3 rows (the first one is what I wanted).

@Query("Match p = (company:Company {name:$name})-[relation:COLLECTS_FROM*0..2]->(secondCompany:Company) RETURN p")

• and fourth one once again 3 companies, instead of a single one (the first one is what I wanted)

@Query("MATCH (company:Company {name:$name} ) WITH company MATCH p=(company)-[COLLECTS_FROM*0..2]->(m) RETURN p")

And that's basically it. I feel like I'm close, but a little detail is missing. Maybe instead of trying to fix the query, I should somehow limit the result to get the first row from the Query? Any help will be really appreciated.

update after receiving @cybersam answer:

The general purpose of this query is to create a tree graph, that's why I would like to get a company with their sub-companies inside (and companies in the sub-companies), not only the parent one.

I could use second, or third approach and just grab the first element on the server side:

result.stream()
.findFirst()
.orElseThrow();

but I believe it should be able to do it on the service side.

Answer 1

To return the Company with the specified name if it is connected by a path of 1 to 2 COLLECTS_FROM relationships to descendant Company nodes:

@Query("MATCH p=(c:Company {name:$name})-[:COLLECTS_FROM*..2]->(:Company) WHERE ALL(n IN NODES(p) WHERE 'Company' IN LABELS(n)) RETURN c")
Company customerWithCustomDepth(String name);

I assume that you do not want to get a result if there are no such descendants, which is why the variable-length relationship pattern does not use *0..2.

Note: If you want to test for paths of exactly length 2, change ..2 to just 2.

The corresponding query for paths of any length can be obtained by just changing ..2 to ... However, variable-length relationship pattern with no upper bound are not recommended, since they can take "forever" to run or run out of memory.