CODEWITHSUNDEEP
neo4jcypher
Jon Peterson
Jon Peterson

Reputation: 2996

Neo4j Cypher query to return one row per node property

Given the data:

I want to be able to query for any given time and see what the last sporting equipment and car Adam bought. The THING nodes have a type property (car, sporting equipment, etc).

This works...

MATCH (person:Person {name: {name})
OPTIONAL MATCH (person)-[bought:BOUGHT]-(thing:THING)
WHERE bought.date <= {date}
RETURN *
ORDER BY thing.date DESC

...but it returns everything he's ever bought before the specified date. I could just grab the first row for each type in my application, but this is a contrived example, and in reality my actual application could see thousands of entries like this before aging off.

If I put...

LIMIT 1

...at the end, then I only get the last bought THING (as opposed to the last one of each type).

How can I build a query that will provide me with something like:

Query: Tell me about Adam and the last things he bought as of 2011?

Response:

person                  | bought           | thing
{name: 'Adam', age: 38} | {date: 2010}     | {type: 'sporting equipment', name: 'Tennis Racket'}
{name: 'Adam', age: 38} | {date: 2006}     | {type: 'car', name: 'Honda Civic'}

Upvotes: 0

Views: 721

Answers (1)

stdob--
stdob--

Reputation: 29172

You can aggregate by thing.type:

MATCH (person:Person {name: {name}})
OPTIONAL MATCH (person)-[bought:BOUGHT]-(thing:THING)
WHERE bought.date <= {date}
WITH person, 
     thing, 
     bought ORDER by bought.date DESC
WITH person, 
     thing.type as type, 
     HEAD(collect([thing, bought])) as tmp
RETURN person, 
       tmp[1] as bought, 
       tmp[0] as thing

Upvotes: 2

Related Questions