neo4jcypherneo4j-apocneo4jclient

Neo4j Sorting after the Union on the combined statement


I have two neo4j cypher queries.

one:

MATCH (u:ClientUser {name: 'Karthick J [Karthick.J@pfizer.com]'})--(b)-[:BRAND_TAG_RELATESTOTHETOPICTAG|BUSINESSAREA_TAG_RELATESTOTHETOPICTAG|MARKET_TAG_RELATESTOTHETOPICTAG|SKILL_TAG_HASTHERELATEDTOPICTAG|INTEREST_TAG_HASTHERELATEDTOPICTAG]->(t:Tag)<-[:CONTENTPIECE_TAG_HASTHERELATEDCONTENTKEYWORD]-(p:ContentPiece)
WHERE NOT (u)-[:CLIENTUSER_CONTENTPIECE_ISTHEAUTHORFORTHECONTENTPIECE]-(p)
WITH u, b, p, t
ORDER BY p.publishedDate DESC
WITH u, p, collect(DISTINCT b.name) AS becauseYouWorkOn, collect(DISTINCT t.name) AS hasTag
RETURN DISTINCT{
becauseYouWorkOn: becauseYouWorkOn, 
recommendedArticleTitle: p.marcelDisplayName, 
recommendedArticleId: p.contentId,
publishDate: p.publishedDate,
hasTag: hasTag,
contentType: labels(p)
} AS result

Two:

MATCH (u:ClientUser{name: 'Karthick J [Karthick.J@pfizer.com]'})-[:CLIENTUSER_BRAND_WORKSONTHEBRAND|CLIENTUSER_BUSINESSAREA_WORKSINTHEBUSINESSAREA|CLIENTUSER_MARKET_SPECIALIZESINTHEMARKET]->(b)<-[:POST_BUSINESSAREA_ISASSOCIATEDWITHTHEBUSINESSAREA|POST_BRAND_ISASSOCIATEDWITHTHEBRAND|POST_BRAND_ISASSOCIATEDWITHTHEADDITIONALBRANDS|POST_MARKET_ISASSOCIATEDWITHTHEMARKET]-(n)
WHERE NOT (u)-[:CLIENTUSER_POST_HASPUBLISHEDTHEPOST]->(n)
WITH u, b, n
ORDER BY n.publishedDate desc
WITH u, n, collect(DISTINCT b.name) as becauseYouWorkOn, collect(DISTINCT b.name) as hasTag
RETURN DISTINCT{
becauseYouWorkOn: becauseYouWorkOn, 
recommendedArticleTitle: n.title, 
recommendedArticleId: n.postId,
publishDate: n.publishedDate,
hasTag: hasTag,
contentType: labels(n)
} AS result

I want to merge both the queries return one unified list and the combined list needs to be sorted based on publishedDate. The query I have written is:

// Query for ContentPiece
MATCH (u:ClientUser {name: 'Karthick J [Karthick.J@pfizer.com]'})--(b)-[:BRAND_TAG_RELATESTOTHETOPICTAG|BUSINESSAREA_TAG_RELATESTOTHETOPICTAG|MARKET_TAG_RELATESTOTHETOPICTAG|SKILL_TAG_HASTHERELATEDTOPICTAG|INTEREST_TAG_HASTHERELATEDTOPICTAG]->(t:Tag)<-[:CONTENTPIECE_TAG_HASTHERELATEDCONTENTKEYWORD]-(p:ContentPiece)
WHERE NOT (u)-[:CLIENTUSER_CONTENTPIECE_ISTHEAUTHORFORTHECONTENTPIECE]-(p)
WITH u, b, p, t
ORDER BY p.publishedDate DESC
WITH u, p, collect(DISTINCT b.name) AS becauseYouWorkOn, collect(DISTINCT t.name) AS hasTag, p.publishedDate AS date
RETURN {
  becauseYouWorkOn: becauseYouWorkOn, 
  recommendedArticleTitle: p.marcelDisplayName, 
  recommendedArticleId: p.contentId,
  publishDate: date,
  hasTag: hasTag,
  contentType: labels(p)
} AS result

UNION

// Query for Posts
MATCH (u:ClientUser {name: 'Karthick J [Karthick.J@pfizer.com]'})-[:CLIENTUSER_BRAND_WORKSONTHEBRAND|CLIENTUSER_BUSINESSAREA_WORKSINTHEBUSINESSAREA|CLIENTUSER_MARKET_SPECIALIZESINTHEMARKET]->(b)<-[:POST_BUSINESSAREA_ISASSOCIATEDWITHTHEBUSINESSAREA|POST_BRAND_ISASSOCIATEDWITHTHEBRAND|POST_BRAND_ISASSOCIATEDWITHTHEADDITIONALBRANDS|POST_MARKET_ISASSOCIATEDWITHTHEMARKET]-(n)
WHERE NOT (u)-[:CLIENTUSER_POST_HASPUBLISHEDTHEPOST]->(n)
WITH u, b, n
ORDER BY n.publishedDate DESC
WITH u, n, collect(DISTINCT b.name) AS becauseYouWorkOn, collect(DISTINCT b.name) AS hasTag, n.publishedDate AS date
RETURN {
  becauseYouWorkOn: becauseYouWorkOn, 
  recommendedArticleTitle: n.title, 
  recommendedArticleId: n.postId,
  publishDate: date,
  hasTag: hasTag,
  contentType: labels(n)
} AS result
ORDER BY result.publishDate DESC

The problem with the above query is, since this is a union, it does the sorting on the first list first, and then the after union list as last. What I want to do is sort the combined list, Post and ContentPiece combined and then sorted by publishedDate. Any help is highly appreciated.


Solution

  • [UPDATED]

    If you wrap your UNION query in a CALL subquery, you can perform post-union processing on the combined results to sort just once.

    For example:

    MATCH (u:ClientUser {name: 'Karthick J [Karthick.J@pfizer.com]'}
    CALL {
        // Query for ContentPiece
        WITH u
        MATCH (u)--(b)-[:BRAND_TAG_RELATESTOTHETOPICTAG|BUSINESSAREA_TAG_RELATESTOTHETOPICTAG|MARKET_TAG_RELATESTOTHETOPICTAG|SKILL_TAG_HASTHERELATEDTOPICTAG|INTEREST_TAG_HASTHERELATEDTOPICTAG]->(t:Tag)<-[:CONTENTPIECE_TAG_HASTHERELATEDCONTENTKEYWORD]-(p:ContentPiece)
        WHERE NOT (u)-[:CLIENTUSER_CONTENTPIECE_ISTHEAUTHORFORTHECONTENTPIECE]-(p)
        WITH u, p, collect(DISTINCT b.name) AS becauseYouWorkOn, collect(DISTINCT t.name) AS hasTag, p.publishedDate AS date
        RETURN {
        becauseYouWorkOn: becauseYouWorkOn, 
        recommendedArticleTitle: p.marcelDisplayName, 
        recommendedArticleId: p.contentId,
        publishDate: date,
        hasTag: hasTag,
        contentType: labels(p)
        } AS result
    
        UNION
    
        // Query for Posts
        WITH u
        MATCH (u)-[:CLIENTUSER_BRAND_WORKSONTHEBRAND|CLIENTUSER_BUSINESSAREA_WORKSINTHEBUSINESSAREA|CLIENTUSER_MARKET_SPECIALIZESINTHEMARKET]->(b)<-[:POST_BUSINESSAREA_ISASSOCIATEDWITHTHEBUSINESSAREA|POST_BRAND_ISASSOCIATEDWITHTHEBRAND|POST_BRAND_ISASSOCIATEDWITHTHEADDITIONALBRANDS|POST_MARKET_ISASSOCIATEDWITHTHEMARKET]-(n)
        WHERE NOT (u)-[:CLIENTUSER_POST_HASPUBLISHEDTHEPOST]->(n)
        WITH u, n, collect(DISTINCT b.name) AS becauseYouWorkOn, collect(DISTINCT b.name) AS hasTag, n.publishedDate AS date
        RETURN {
        becauseYouWorkOn: becauseYouWorkOn, 
        recommendedArticleTitle: n.title, 
        recommendedArticleId: n.postId,
        publishDate: date,
        hasTag: hasTag,
        contentType: labels(n)
        } AS result
    }
    RETURN result
    ORDER BY result.publishDate DESC