Hello and thank you for helping :) The following query works well but is terribly clumsy, I didn't manage to make a WITH subquery get the right results, could anybody have a look ? Thanks a lot !!
MATCH f= (a:Border)<-[:ISSUED_BY]-(d:Declaration)<-[r:PRESENTS]-(p:Person) WHERE a.borderEntry= "Heathrow" AND d.date="2024-01-01"
WITH count(f) as Passengers
MATCH g=(a:Border)<-[:ISSUED_BY-(d:Declaration)<-[r:PRESENTS]-(p:Person) WHERE a.borderEntry= "Heathrow" AND d.date="2024-01-01" AND r.goal="Turism"
RETURN Passengers, count(g) AS Turists
As you can see, I am just repeating the initial MATCH with more details after the WITH clause.
In this particular case, you may not need subqueries, we can employ a different trick so you can get the total count and the turist count.
You can actually do two aggregations (counts, in this case) at the same time, and when you aggregate on a value, nulls are not counted, so we can project out the r.goal property with a CASE such that all non-Turism values will be null, and count that to get the turist count:
MATCH (a:Border)<-[:ISSUED_BY]-(d:Declaration)<-[r:PRESENTS]-(p:Person)
WHERE a.borderEntry= "Heathrow" AND d.date="2024-01-01"
WITH p, CASE WHEN r.goal = "Turism" THEN 1 ELSE NULL END as turist
RETURN count(p) as total, count(turist) AS Turists
2 Likes
I thank you sincerely. It works perfectly !! I was not at all aware of this possibility and will learn about it from what you wrote. I am also grateful for your prompt response. Thanks again.
If I may ask a last question, would it be possible to solve the problem just using WITH subqueries ? I hope I am not disturbing with this supplementary question, as the topic is already solved !! Thank you.
I don't think a subquery appropriate for your use case, as it is used to pass a single row, process the row, and return one or more records. The result returned from the subquery is appended to the row that was passed to it. If the subquery created more rows, then the row data is repeated.
The following is another approach using list comprehension.
WITH [(:Border{borderEntry:"Heathrow"})<-[:ISSUED_BY]-(:Declaration{date:"2024-01-01"})<-[r:PRESENTS]-(:Person) | r] as results
RETURN
size(results) as Passengers,
size([r in results where r.goal="Turism"]) as Turists
I do think @andrew_bowman's solution is more elegant and understandable.
Again thanks a lot for your answer !! I just wanted to know IF there was an alternative solution just using WITH :) I have both possibilities now, and am very happy to have got them, as it allows me to improve in my rather light knowledge of Neo4j. Very impressed by the potential of this forum and the niceness of the people replying !!
1 Like
Pretty much anything you can do in a RETURN you can also do in a WITH, so if there are additional operations you need to perform after, you can do the count() aggregations in a WITH clause and then continue to to work with the results until a later RETURN.
Thank you so much, I will bear that in mind. I was sometimes puzzled by the fact that after a WITH clause, only the variables declared in the WITH are transferred to the subsequent operations: if I need a count() in the WITH clause, as in the example above, for example, and then add details to what has been counted to make a different count, I couldn't figure out how to refer to the initial MATCH and add some details to it (in my case, it was the "goal" property of the "PRESENTS" relation).