Neo4j Cypher Union合并结果后按publishedDate全局排序求助

问题说明 #

现有两个分别查询ContentPiece和Post的Cypher语句，需要合并结果并按publishedDate降序排列。原查询使用UNION时，子查询内的排序仅作用于各自结果，合并后无法实现整体排序。

修改后的查询语句 #

// 查询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, p, collect(DISTINCT b.name) AS becauseYouWorkOn, collect(DISTINCT t.name) AS hasTag
RETURN {
  becauseYouWorkOn: becauseYouWorkOn, 
  recommendedArticleTitle: p.marcelDisplayName, 
  recommendedArticleId: p.contentId,
  publishDate: p.publishedDate,
  hasTag: hasTag,
  contentType: labels(p)
} AS result

UNION

// 查询Post数据
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, n, collect(DISTINCT b.name) AS becauseYouWorkOn, collect(DISTINCT b.name) AS hasTag
RETURN {
  becauseYouWorkOn: becauseYouWorkOn, 
  recommendedArticleTitle: n.title, 
  recommendedArticleId: n.postId,
  publishDate: n.publishedDate,
  hasTag: hasTag,
  contentType: labels(n)
} AS result

// 对合并后的所有结果统一排序
ORDER BY result.publishDate DESC

关键修改点 #

1. 移除子查询内的无效排序：原查询中两个子查询的ORDER BY不会影响最终合并结果的顺序，反而会增加不必要的计算开销，直接删除即可。
2. 将排序放在UNION之后：把ORDER BY result.publishDate DESC放在整个UNION语句的末尾，确保合并后的所有结果统一按发布日期降序排列。

如果不需要对合并结果去重，可以将UNION替换为UNION ALL，这样性能会更好（UNION默认会对结果去重，UNION ALL则保留所有结果）。

内容的提问来源于stack exchange，提问作者Strayhorn