CODEWITHSUNDEEP
neo4jcypher
AndyP1970
AndyP1970

Reputation: 205

How to get max value in cypher subquery

I've read the questions about subqueries but still stuck with this use case.

I have Documents that contain one or more keywords and each document has linked user comments with a status property. I want to get just the most recent status (if it exists) returned for each document in the query. If I run a query like the following, I just get one row. 

    MATCH (d:Document)-[:Keys]->(k:Keywords)
    WITH d,k
    OPTIONAL MATCH (d)--(c:Comments)
    ORDER BY c.created DESC LIMIT 1
    RETURN d.Title as Title, k.Word as Keyword, c.Status as Status

I have hundreds of documents I want to return with the latest status like:

Title                      Keyword  Status
War in the 19th Century    WWI      Reviewed
War in the 19th Century    Weapons  Reviewed
The Great War              WWI      Pending
World War I                WWI      <null>

I have tried multiple queries using WITH clause but no luck yet. Any suggestions would be appreciated.

Upvotes: 1

Views: 524

Answers (2)

InverseFalcon
InverseFalcon

Reputation: 30397

We've got a knowledge base article explaining how to limit results of a match per-row, that should give you a few good options.

EDIT

Here's a full example, using apoc.cypher.run() to perform the limited subquery.

MATCH (d:Document)-[:Keys]->(k:Keywords)
WITH d, COLLECT(k.Word) AS Keywords
// collect keywords first so each document on a row
CALL apoc.cypher.run('
 OPTIONAL MATCH (d)--(c:Comments)
 RETURN c
 ORDER BY c.created DESC
 LIMIT 1
', {d:d}) YIELD value
RETURN d.Title as Title, Keywords, value.c.status as Status

Upvotes: 1

cybersam
cybersam

Reputation: 66967

This query should do what you probably intended:

MATCH (d:Document)-[:Keys]->(k:Keywords)
OPTIONAL MATCH (d)--(c:Comments)
WITH d, COLLECT(k.Word) AS Keywords, c
ORDER BY c.created DESC
WHERE c IS NOT NULL
RETURN d.Title as Title, Keywords, COLLECT(c)[0] AS Status

Since a comment is related to a document, and not a document/keyword pair, it makes more sense to return a collection of keywords for each Title/Status pair. Your original query, if it had worked, would have returned the same Title/Status pair multiple times, each time with a different keyword.

Upvotes: 2

Related Questions