如何解决Drizzle ORM MySQL关联定义中的TypeScript类型错误

问题说明 #

在定义employees与strengths表的多对多关联时，中间表employee_strength的relations代码出现类型错误：

Type 'any[]' is not assignable to type 'Record<string, Relation>'.
Index signature for type 'string' is missing in type 'any[]'.ts(2322)

同时需要实现：查询员工数据时，获取对应strength的标题（而非ID），且按employee_strength的order字段排序strength数组。

现有表及关联定义 #

export const employees = mysqlTable('employees', {
  id: serial('id').notNull().primaryKey(),
  // 其他字段
});

export const strengths = mysqlTable('strengths', {
  id: serial('id').notNull().primaryKey(),
  title: varchar('title', { length: 255 }),
});

export const employee_strength = mysqlTable('employee_strength', {
  id: serial('id').notNull().primaryKey(),
  employee_id: bigint('employee_id', { mode: 'number' }).references(() => employees.id),
  strength_id: bigint('strength_id', { mode: 'number' }).references(() => strengths.id),
  order: int('order'),
});

export const employeeRelations = relations(employees, ({ many }) => ({
  employeesToStrengths: many(employee_strength),
}));

export const strengthsRelations = relations(strengths, ({ many }) => ({
  employeesToStrengths: many(employee_strength),
}));

// 报错的关联定义
export const employeeStrengthRelations = relations(employee_strength, ({ one }) => ([
  strength: one(strengths, {
    fields: [employee_strength.strength_id],
    references: [strengths.id],
  }),
  employee: one(employees, {
    fields: [employee_strength.employee_id],
    references: [employees.id],
  }),
]));

错误修复步骤 #

1. 修正中间表的relations定义 #

relations函数的回调必须返回对象（键值对结构），而不是数组。把方括号[]改成大括号{}即可解决类型错误：

export const employeeStrengthRelations = relations(employee_strength, ({ one }) => ({
  strength: one(strengths, {
    fields: [employee_strength.strength_id],
    references: [strengths.id],
  }),
  employee: one(employees, {
    fields: [employee_strength.employee_id],
    references: [employees.id],
  }),
}));

2. 优化员工表的多对多关联（可选但推荐） #

直接在employeeRelations里定义与strengths的多对多关联，跳过手动遍历中间表的步骤，查询更简洁：

export const employeeRelations = relations(employees, ({ many }) => ({
  // 保留原有中间表关联（如果需要操作中间表字段）
  employeesToStrengths: many(employee_strength),
  // 新增直接关联strengths的多对多关系
  strengths: many(strengths, {
    through: employee_strength,
    references: [employee_strength.strength_id],
    fields: [employee_strength.employee_id],
  }),
}));

实现目标查询 #

要获取按order排序的strength.title数组，在查询时需要：

• 关联中间表并包含strength数据
• 按中间表的order字段排序
• 可选：只提取title字段

方案1：基于原有中间表关联查询 #

const results = await this.db.query.employees.findMany({
  with: {
    employeesToStrengths: {
      // 关联strength表
      with: {
        strength: true,
      },
      // 按中间表的order字段排序
      orderBy: (es) => es.order,
    },
  },
  // 可选：格式化结果，提取排序后的title数组
  map: (employee) => ({
    ...employee,
    strengthTitles: employee.employeesToStrengths.map(es => es.strength.title).filter(Boolean),
  }),
});

方案2：基于优化后的多对多关联查询（需同时排序中间表） #

如果用优化后的直接关联，要保留排序的话，需要在查询时关联中间表并排序：

const results = await this.db.query.employees.findMany({
  with: {
    strengths: {
      // 通过中间表关联时指定排序
      orderBy: (_, { relation }) => relation.order,
    },
  },
});

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