cancel
Showing results for 
Search instead for 
Did you mean: 

Custom parametrized queries in SDN6

damienthewave
Node Link

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?

5 REPLIES 5

damienthewave
Node Link

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()
  
   ...
}

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
Neo4j
Neo4j

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).

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.

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

Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

On November 16 and 17 for 24 hours across all timezones, you’ll learn about best practices for beginners and experts alike.