CODEWITHSUNDEEP
neo4jcypherneo4j-apocneo4jclient
Strayhorn
Strayhorn

Reputation: 729

Neo4j Sorting after the Union on the combined statement

I have two neo4j cypher queries.

one:

MATCH (u:ClientUser {name: 'Karthick J [[email protected]]'})--(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 [[email protected]]'})-[: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 [[email protected]]'})--(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 [[email protected]]'})-[: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.

Upvotes: 0

Views: 41

Answers (1)

cybersam
cybersam

Reputation: 66957

[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 [[email protected]]'}
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

Upvotes: 1

Related Questions