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.
[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