Sequelize自关联一对多表如何递归查询所有上级父对象？

你的自关联设置仅能直接查询单个直接父对象，无法一次性递归获取从子对象到根节点的所有上级。以下是几种高效实现的方案，按优先级排序：

1. 利用数据库递归CTE（最优方案，无额外依赖） #

几乎所有现代数据库（PostgreSQL、MySQL 8+、SQL Server等）都支持递归公共表表达式（WITH RECURSIVE），可通过一次SQL查询直接获取完整的父级链，完全避免多次数据库请求。

以Sequelize为例，自定义查询方法如下：

async function getAllAncestors(childId) {
  return await driveObject.sequelize.query(`
    WITH RECURSIVE ancestor_chain AS (
      -- 起始节点：目标子对象
      SELECT id, parentId, name
      FROM drive_objects
      WHERE id = :childId
      -- 递归逻辑：向上遍历父节点
      UNION ALL
      SELECT do.id, do.parentId, do.name
      FROM drive_objects do
      JOIN ancestor_chain ac ON do.id = ac.parentId
    )
    -- 排除自身，按层级从根到子排序（根节点parentId为null）
    SELECT * FROM ancestor_chain WHERE id != :childId
    ORDER BY parentId NULLS FIRST;
  `, {
    replacements: { childId },
    model: driveObject, // 自动映射为DriveObject实例
    type: driveObject.sequelize.QueryTypes.SELECT
  });
}

这种方式仅需一次数据库请求，性能最优，无需修改现有模型或新增表。

2. 应用层递归查询（兼容老版本数据库） #

如果你的数据库不支持递归CTE（如MySQL 5.x），可在应用层实现递归查询，每次获取当前节点的父级，直到parentId为null。虽然会产生多次数据库请求，但可通过缓存或限制层级减少开销：

async function getAllAncestors(childId, ancestors = []) {
  const current = await driveObject.findByPk(childId, {
    attributes: ['id', 'parentId', 'name']
  });
  if (!current) return ancestors;
  
  if (current.parentId) {
    const parent = await driveObject.findByPk(current.parentId);
    ancestors.unshift(parent); // 按从根到子的顺序排列
    return getAllAncestors(current.parentId, ancestors);
  }
  return ancestors;
}

3. 优化模型结构（物化路径，适合高频查询场景） #

如果系统需要频繁查询父级链，可给DriveObject模型新增ancestorPath字段（比如用字符串存储父ID链"1,3,5"，或JSON数组[1,3,5]），每次创建或移动文件夹时更新该字段。

查询时直接通过该字段筛选并获取所有父节点：

// 假设ancestorPath为JSON数组类型
async function getAllAncestors(childId) {
  const child = await driveObject.findByPk(childId, { attributes: ['ancestorPath'] });
  if (!child?.ancestorPath) return [];
  
  return driveObject.findAll({
    where: { id: child.ancestorPath }
  });
}

这种方式查询速度极快，但需要维护ancestorPath字段的一致性（比如移动文件夹时需更新所有子节点的路径），适合读写比例极高的场景。

是否需要第三方包？ #

不需要。无论是数据库原生递归查询还是应用层递归，都可通过现有ORM（如Sequelize）或原生SQL实现，无需引入额外第三方包。

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