2

I try to replicate the behaviour of the following SQL query in neo4j

DELETE FROM history
WHERE history.name = $modelName AND id NOT IN (
   SELECT history.id
   FROM history
   JOIN model ON model.id = history.model_id
   ORDER BY created DESC
   LIMIT 10
)

I tried a lot of different queries, but basically I'm always struggling to incorporate finding the TOP-k elements. That's the closest I got to a solution.

MATCH (h:HISTORY)-[:HISTORY]-(m:MODEL)
WHERE h.name = $modelName
WITH h
MATCH (t:HISTORY)-[:HISTORY]-(m:MODEL)
WITH t ORDER BY t.created DESC LIMIT 10
WHERE NOT h IN t 
DELETE h

With that query I get the error expected List<T> but was Node for the line WITH t ORDER BY t.created DESC LIMIT 10.
I tried changing it it COLLECT(t) AS t but then the error is expected Any, Map, Node or Relationship but was List<Node>.

So I'm pretty much stuck. Any idea how to write this query in Cypher?

joe776
  • 1,106
  • 14
  • 23

1 Answers1

3

Following that approach, you should reverse the order, matching to your top-k nodes, collecting them, and performing the match where the nodes matched aren't in the collection.

MATCH (t:HISTORY)-[:HISTORY]-(:MODEL)
WITH t ORDER BY t.created DESC LIMIT 10
WITH collect(t) as saved

MATCH (h:HISTORY)-[:HISTORY]-(:MODEL)
WHERE h.name = $modelName
AND NOT h in saved
DETACH DELETE h
InverseFalcon
  • 29,576
  • 4
  • 38
  • 51
  • Works like a charm, thank you! Just while walking away from the laptop I had the thought that reversing the order might be a solution. Also the `DETACH DELETE`is of course important here. Had to smile as your username fits the solution :) – joe776 Jul 28 '17 at 11:00