使用TypeScript/JS客户端更新EdgeDB对象：关联与嵌套处理问题

一、整合教育详情的删除操作 #

当前代码仅实现了教育详情的插入与更新，未处理候选人不再关联的教育记录。根据业务场景，可选择以下两种方案：

方案1：仅解除候选人与过时教育记录的关联（保留Education数据） #

如果Education是可被多个候选人共享的资源，直接更新候选人的education_details关联列表即可——EdgeDB在设置multi关联为新列表时，会自动移除原有列表中不存在的关联：

// 原有教育详情upsert逻辑不变
let educationDetails = await e.params({ items: e.json }, (params) => {
  return e.for(e.json_array_unpack(params.items), (item) => {
    return e.insert(e.Education, {
      institution_name: e.cast(e.str, item.institution_name),
      course_name: e.cast(e.str, item.course_name),
      pass_percentage: e.cast(e.str, item.pass_percentage),
      course_start_month: e.cast(e.str, item.course_start_month),
      course_end_month: e.cast(e.str, item.course_end_month),
      course_start_year: e.cast(e.str, item.course_start_year),
      course_end_year: e.cast(e.str, item.course_end_year)
    }).unlessConflict(education => ({
      on: [education.institution_name, education.course_name],
      else: e.update(e.Education, () => ({
        set: {
          course_start_month: e.cast(e.str, item.course_start_month),
          course_end_month: e.cast(e.str, item.course_end_month),
          course_start_year: e.cast(e.str, item.course_start_year),
          course_end_year: e.cast(e.str, item.course_end_year),
          pass_percentage: e.cast(e.str, item.pass_percentage)
        }
      }))
    }));
  });
}).run(client, { items: education_details });

// 更新候选人时直接设置新关联列表，旧关联自动移除
await e.update(e.Candidate, (candidate) => ({
  filter: e.op(candidate.id, "=", e.uuid(id)),
  set: {
    first_name,
    last_name,
    dob: e.str(dob),
    primary_contact_number,
    alternate_contact_number,
    hobbies,
    profile_summary,
    education_details: e.select(educationDetails),
  }
})).run(client);

方案2：删除候选人不再关联的专属Education记录 #

如果Education是该候选人专属（不会被其他候选人引用），需先清理过时的教育记录：

// 1. 提取传入教育详情的唯一标识（课程名+机构名）
const incomingEducationKeys = education_details.map(item => 
  `${item.course_name}|${item.institution_name}`
);

// 2. 删除当前候选人关联的、不在传入列表的Education记录
await e.delete(e.Education, (edu) => ({
  filter: e.op(
    e.op(edu.course_name ++ "|" ++ edu.institution_name, "not in", incomingEducationKeys),
    "and",
    e.exists(e.Candidate, (c) => ({
      filter: e.op(c.id, "=", e.uuid(id)),
      predicate: e.op(c.education_details, "@=", edu)
    }))
  )
})).run(client);

// 3. 执行原有教育详情upsert和候选人更新逻辑（同方案1）
let educationDetails = await e.params({ items: e.json }, (params) => {
  return e.for(e.json_array_unpack(params.items), (item) => {
    return e.insert(e.Education, {
      institution_name: e.cast(e.str, item.institution_name),
      course_name: e.cast(e.str, item.course_name),
      pass_percentage: e.cast(e.str, item.pass_percentage),
      course_start_month: e.cast(e.str, item.course_start_month),
      course_end_month: e.cast(e.str, item.course_end_month),
      course_start_year: e.cast(e.str, item.course_start_year),
      course_end_year: e.cast(e.str, item.course_end_year)
    }).unlessConflict(education => ({
      on: [education.institution_name, education.course_name],
      else: e.update(e.Education, () => ({
        set: {
          course_start_month: e.cast(e.str, item.course_start_month),
          course_end_month: e.cast(e.str, item.course_end_month),
          course_start_year: e.cast(e.str, item.course_start_year),
          course_end_year: e.cast(e.str, item.course_end_year),
          pass_percentage: e.cast(e.str, item.pass_percentage)
        }
      }))
    }));
  });
}).run(client, { items: education_details });

await e.update(e.Candidate, (candidate) => ({
  filter: e.op(candidate.id, "=", e.uuid(id)),
  set: {
    first_name,
    last_name,
    dob: e.str(dob),
    primary_contact_number,
    alternate_contact_number,
    hobbies,
    profile_summary,
    education_details: e.select(educationDetails),
  }
})).run(client);

二、优雅处理嵌套Projects类型的更新 #

首先完善Schema，定义Project类型并与Education建立关联：

type Project {
  required project_name: str;
  required project_description: str;
  required education: Education;
  # 确保同一教育记录下的项目名称唯一
  constraint exclusive on ((.project_name, .education));
}

type Education {
  required course_name: str;
  required institution_name: str;
  required course_start_month: str;
  required course_start_year: str;
  required course_end_month: str;
  required course_end_year: str;
  pass_percentage: str;
  constraint exclusive on ((.course_name, .institution_name));
  multi projects: Project;
}

在处理Education的upsert时，嵌套实现Projects的增删改：

let educationDetails = await e.params({ items: e.json }, (params) => {
  return e.for(e.json_array_unpack(params.items), (item) => {
    return e.insert(e.Education, {
      institution_name: e.cast(e.str, item.institution_name),
      course_name: e.cast(e.str, item.course_name),
      pass_percentage: e.cast(e.str, item.pass_percentage),
      course_start_month: e.cast(e.str, item.course_start_month),
      course_end_month: e.cast(e.str, item.course_end_month),
      course_start_year: e.cast(e.str, item.course_start_year),
      course_end_year: e.cast(e.str, item.course_end_year),
      // 嵌套处理Projects的新增/更新
      projects: e.for(e.json_array_unpack(item.projects), (proj) => {
        return e.insert(e.Project, {
          project_name: e.cast(e.str, proj.project_name),
          project_description: e.cast(e.str, proj.project_description),
          education: e.select(e.Education, (edu) => ({
            filter: e.op(
              edu.course_name, "=", e.cast(e.str, item.course_name),
              "and",
              edu.institution_name, "=", e.cast(e.str, item.institution_name)
            )
          }))
        }).unlessConflict(proj => ({
          on: [proj.project_name, proj.education],
          else: e.update(e.Project, () => ({
            set: { project_description: e.cast(e.str, proj.project_description) }
          }))
        }));
      })
    }).unlessConflict(education => ({
      on: [education.institution_name, education.course_name],
      else: e.update(e.Education, () => ({
        set: {
          course_start_month: e.cast(e.str, item.course_start_month),
          course_end_month: e.cast(e.str, item.course_end_month),
          course_start_year: e.cast(e.str, item.course_start_year),
          course_end_year: e.cast(e.str, item.course_end_year),
          pass_percentage: e.cast(e.str, item.pass_percentage),
          // 更新Education时同步清理过时项目并upsert新项目
          projects: (() => {
            // 删除当前教育记录中不在传入列表的项目
            e.delete(e.Project, (p) => ({
              filter: e.op(
                p.project_name, "not in", e.json_array_unpack(item.projects).map(p => p.project_name),
                "and",
                p.education.id = education.id
              )
            }));
            // 新增/更新传入的项目
            return e.for(e.json_array_unpack(item.projects), (proj) => {
              return e.insert(e.Project, {
                project_name: e.cast(e.str, proj.project_name),
                project_description: e.cast(e.str, proj.project_description),
                education: education
              }).unlessConflict(proj => ({
                on: [proj.project_name, proj.education],
                else: e.update(e.Project, () => ({
                  set: { project_description: e.cast(e.str, proj.project_description) }
                }))
              }));
            });
          })()
        }
      }))
    }));
  });
}).run(client, { items: education_details });

// 候选人更新逻辑同前
await e.update(e.Candidate, (candidate) => ({
  filter: e.op(candidate.id, "=", e.uuid(id)),
  set: {
    first_name,
    last_name,
    dob: e.str(dob),
    primary_contact_number,
    alternate_contact_number,
    hobbies,
    profile_summary,
    education_details: e.select(educationDetails),
  }
})).run(client);

三、存JSON vs 关联类型的对比 #

将教育详情存为JSON是一种简化方案，但不推荐，核心原因：

• 类型不安全：无法保证JSON内字段的类型、必填性，易出现数据不一致
• 查询能力弱：无法针对教育详情或项目字段做复杂筛选、聚合
• 缺乏约束：无法设置唯一性、关联关系等规则，难以维护数据完整性
• 维护成本高：嵌套JSON的更新逻辑繁琐，无法利用EdgeDB的ORM特性

使用关联类型（Education、Project）可充分发挥EdgeDB的优势：类型安全、强约束、灵活查询、原生嵌套操作支持，是更简洁、可维护的方案。

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