使用OPTIONAL MATCH时Neo4J用户账号软删除失效问题排查

问题描述 #

实现用户账号软删除功能时遇到异常：当用户没有Post或Comment节点时，User节点的deleted属性无法被设置为true。

需求 #

• 用户可能存在或不存在Post、Comment节点，需通过OPTIONAL MATCH匹配这些节点
• 若存在Post，将所有Post的deleted设为true
• 若存在Comment，执行DETACH DELETE删除所有评论及其关联关系
• 无论是否存在关联节点，必须将匹配到的User节点的deleted设为true

原代码 #

import neo4j from "neo4j-driver"

const softDeleteAccount = async (userId) => {

  let driver = neo4j.driver(
    process.env.NEO4J_URL,
    neo4j.auth.basic(process.env.NEO4J_USERNAME, process.env.NEO4J_PASSWORD)
  )

  let session = driver.session()

  try {
    let query = `
          MATCH (user:User {userId: $userId})       
          OPTIONAL MATCH (posts:Post {userId: $userId})        // User may not have any posts
          OPTIONAL MATCH (comments:Comment {userId: $userId})  // User may not have any comments
          WITH user, posts, comments
          SET posts.deleted = true, user.deleted = true 
                            // ^ for all posts & user, set deleted = true
          DETACH DELETE comments         // Delete comments & their relationships
          `
    await session.executeWrite((tx) =>
      tx.run(query, {
        userId,
      })
    )
  } catch (err) {
    console.log(err)

    // return error
  } finally {
    await session.close()
  }

  // on application exit:
  await driver.close()
}

export { softDeleteAccount }

问题分析 #

原Cypher查询的核心问题：

1. 当没有Post节点时，posts变量为null，执行SET posts.deleted = true会触发null值错误，导致整个SET语句失败，进而User的deleted属性也无法被设置
2. 尽管DETACH DELETE comments在无Comment时不会报错，但前面的SET语句已经因为posts为null终止了执行流程

修改方案 #

调整Cypher查询逻辑，确保User的属性设置独立执行，同时用安全的方式处理关联节点：

import neo4j from "neo4j-driver"

const softDeleteAccount = async (userId) => {

  let driver = neo4j.driver(
    process.env.NEO4J_URL,
    neo4j.auth.basic(process.env.NEO4J_USERNAME, process.env.NEO4J_PASSWORD)
  )

  let session = driver.session()

  try {
    let query = `
          MATCH (user:User {userId: $userId})
          // 优先设置User的deleted属性，确保这一步必执行
          SET user.deleted = true
          // 处理Post：用collect转为集合，无Post时FOREACH不执行，避免null报错
          OPTIONAL MATCH (post:Post {userId: $userId})
          FOREACH (p IN collect(post) | SET p.deleted = true)
          // 处理Comment：无Comment时DETACH DELETE不执行任何操作
          OPTIONAL MATCH (comment:Comment {userId: $userId})
          DETACH DELETE comment
          `
    await session.executeWrite((tx) =>
      tx.run(query, {
        userId,
      })
    )
  } catch (err) {
    console.log(err)

    // return error
  } finally {
    await session.close()
  }

  // on application exit:
  await driver.close()
}

export { softDeleteAccount }

修改说明 #

• 先执行SET user.deleted = true，完全独立于关联节点的处理逻辑，确保User的属性一定会被更新
• 对Post节点使用collect(post)将匹配结果转为集合，再通过FOREACH遍历设置属性：当没有Post时集合为空，FOREACH不会执行，避免null值触发的错误
• 对Comment节点的DETACH DELETE在OPTIONAL MATCH之后执行，无Comment时comment为null，该操作不会产生任何影响，也不会报错

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