是否有一种方法来计算(注释)以获取正确的总计，然后过滤在评论=的地方

Question

我正在添加功能，以返回用WHERE comment.text =~ { mention }提及用户的注释。虽然这正确返回了上述注释，但是当我想要总＃评论时，commentsCount将变为＃上述注释。在应用WHERE comment.text =~ { mention }之前，有什么方法可以将commentsCount计数为＃总注释？

// MENTIONS
  MATCH (user:User {user_id: { user_id }})
  MATCH (post:Post)<-[:AUTHOR]-(author:User)
  WHERE post.createdAt < { before } AND post.text =~ { keyword }
  MATCH (post)-[:HAS_COMMENT]->(comment:Comment)<-[:AUTHOR]-(commentAuthor:User)
  WHERE NOT user.user_id = commentAuthor.user_id AND comment.text =~ { mention }  // filter
  WITH
    post,
    author,
    commentAuthor,
    max(comment.createdAt) as commentCreatedAt,
    count(comment) as commentsPerCommenter
  ORDER BY commentCreatedAt DESC
  WITH
    post,
    author,
    sum(commentsPerCommenter) as commentsCount,
    collect(commentAuthor {.*, commentCreatedAt, commentsCount: commentsPerCommenter}) as commentAuthors
  WITH
    post,
    author,
    commentsCount,  // incorrect # mentioned comments only, want # total comments
    size(commentAuthors) as participantsCount,
    commentAuthors
  UNWIND commentAuthors as commentAuthor
  RETURN collect(post {
    .*,
    author,
    commentAuthor,
    commentsCount,
    participantsCount,
    notificationType: 'mention'
  })[0..{ LIMIT }] as posts

Answer 1

是的，此查询可能会做技巧：

编辑

在过滤之前添加了参与者的计算。这使用模式理解，与APOC过程中的apoc.coll.toSet()配对，以确保列表仅具有不同的值(否则您将获得同一评论者的多次出现，帖子中的每个评论一次(。

  // MENTIONS
  MATCH (user:User {user_id: { user_id }})
  MATCH (post:Post)<-[:AUTHOR]-(author:User)
  WHERE post.createdAt < { before } AND post.text =~ { keyword }
  // get total comments per post
  WITH 
   post, 
   author, 
   user, 
   size((post)-[:HAS_COMMENT]->()) as commentsCount,
   size(apoc.coll.toSet(
     [(post)-[:HAS_COMMENT]->()<-[:AUTHOR]-(commentAuthor) 
       WHERE author <> commentAuthor | commentAuthor])) as participantsCount
  MATCH (post)-[:HAS_COMMENT]->(comment)<-[:AUTHOR]-(commentAuthor)
  WHERE user <> commentAuthor AND comment.text =~ { mention }  // filter
  WITH
    post,
    author,
    commentsCount,
    participantsCount,
    commentAuthor,
    max(comment.createdAt) as commentCreatedAt,
    count(comment) as commentsPerCommenter
  ORDER BY commentCreatedAt DESC
  WITH
    post,
    author,
    commentsCount,
    participantsCount,
    collect(commentAuthor {.*, commentCreatedAt, commentsCount: commentsPerCommenter}) as commentAuthors
  WITH
    post,
    author,
    commentsCount,  
    participantsCount,
    commentAuthors
  UNWIND commentAuthors as commentAuthor
  RETURN collect(post {
    .*,
    author,
    commentAuthor,
    commentsCount,
    participantsCount,
    notificationType: 'mention'
  })[0..{ LIMIT }] as posts