Custom parametrized queries in SDN6

[damienthewave]

Hi, in SDN6 Repository, I was trying to write parametrized queries like the one below:

@Query("MATCH (person: Person)-[:IS_CHILD_OF*\$degree]->(relative: Person) " +
   "WHERE id(person) = \$personId " + 
   "RETURN relative")
fun getRelativesByDegree(personId: Long, degree: Int): List<Person>

This query is to fetch parents of n degree. Apparently I can't do it like this, because it's giving me an exception.

org.springframework.dao.InvalidDataAccessResourceUsageException: Parameter maps cannot be used in MATCH patterns (use a literal map instead, eg. "{id: {param}.id}") (line 1, column 38 (offset: 37))
"MATCH (person: Person)-[:IS_CHILD_OF*$degree]->(relative: Person) WHERE id(person) = $personId RETURN relative"

Of course,I can make serveral of these queries manually but I think it would be better to have one parametrized query.

I remember that I tried to use a query like this, but found it on the internet, that the type can not be parametrized.

@Query("MATCH (a: Person) WHERE id(a) = \$personId " +
   "OPTIONAL MATCH (b: Person) " +
   "WHERE id(b) = \$relativeId " +
   "CREATE (a)-[:\$type]->(b)")
fun (personId: Long, relativeId: Long, type: String)

How can I use the parameters? If I can't, what are the alternatives?

[damienthewave]

I developed the solution - not the clearest but...

@Autowired
private lateinit var template: Neo4jTemplate

...

fun find(personId: Long, type:String, degree: Int) {
   val query = """
       MATCH (person: Person)-[:$type*$degree]->(relative: Person) 
       WHERE id(person) = $personId 
       RETURN relative
   """
   val relatives = template.find(Person::class.java).matching(query).all()
  
   ...
}

[gerrit_meier]

What a timing for a 6 day old question, that we answer both at the same time
Just a note: Please be sure that you somehow prevent query injection by the type parameter.

Because you said: a solution, I assume that you are only interested in the relatives and not even care about a 100% hydration of the data in your case, right? In that case, it is ok to just return the wanted nodes, of course.

[gerrit_meier]

The Cypher error message is at least a little bit confusing. It means that it is also not possible to use a parameter there.

Regarding the query in general: It won't work as you expected because it does not match the domain object Person where you probably defined something like a @Relationship("IS_CHILD_OF") List<Person> relatives.
Given P1->P2->P3 and a degree of 2, you won't get any relatives mapped because the returned relationship would describe itself as a relationship between P2 and P3, but not between P1 and P3.
Having said this, your return clause should look something like RETURN person, collect(missingRelationshipVariableName), collect(relative).

[damienthewave]

Thanks for your answer, @gerrit.meier but I must say that it does work with a constant parameter (for instance 3). I do not need to collect relations, as I needed only the properties.

[gerrit_meier]

That's what I meant. If you are only interested in the relative(s), you are good.