Sequelize TypeScript能否定义无外键的@HasMany/@BelongsTo关联？

我尝试给QuestionModel和AnonymousQuestionModel定义一对@HasMany/@BelongsTo关联，不使用外键以适配自定义的include/join on条件，但即使设置了constraints: false和foreignKeyConstraint: false，仍出现错误：Foreign key for {Model1} is missing on {Model2}。不确定Sequelize和sequelize-typescript是否支持这种无外键的关联场景。

示例代码 #

AnonymousQuestionModel.ts #

export interface AnonymousQuestionAttributes {
    // ...
}

@Table
export class AnonymousQuestionModel
  extends Model<AnonymousQuestionAttributes>
  implements AnonymousQuestionAttributes {
    @AutoIncrement
    @PrimaryKey
    @Column({ type: DataType.BIGINT })
    id!: number

   @Column
   email!: string

   @Column
   subject!: string

    @Column
    description!: string

    @BelongsTo(() => QuestionModel, { constraints: false, foreignKeyConstraint: false })
    maybeUserQuestion?: QuestionAttributes | null
}

export interface QuestionAttributes extends Omit<AnonymousQuestionAttributes, 'email'> {
    user: UserAttributes
}

QuestionModel.ts #

@Table
export class QuestionModel extends Model<QuestionAttributes> implements QuestionAttributes {
    @AutoIncrement
    @PrimaryKey
    @Column({ type: DataType.BIGINT })
    id!: number

   @Column
   subject!: string

    @Column
    description!: string

    @ForeignKey(() => UserModel)
    userId!: number

    @BelongsTo(() => UserModel)
    user!: UserAttributes

    @HasMany(() => AnonymousQuestionModel, { constraints: false, foreignKeyConstraint: false })
    maybeDuplicateQuestions: AnonymousQuestionAttributes[] = []
}

QuestionsService.ts #

export class QuestionService {
  constructor(protected readonly questionModel: ModelStatic<QuestionModel>) {
  }

  findAllWithDuplicates(opts: FindOptions<QuestionAttributes>) {
    return this.questionModel.findAll({
        ...opts,
        include: [
            {
                association: 'user',
            },
            {
                association: 'maybeDuplicateQuestions',
                on: {
                   [Op.and]: [
                        Sequelize.where(
                           Sequelize.col('QuestionModel.user.email'),
                           Op.eq,
                           Sequelize.col('maybeDuplicateQuestions.email')
                    ),
                    Sequelize.where(
                        Sequelize.col('QuestionModel.subject'),
                        Op.eq,
                        Sequelize.col('maybeDuplicateQuestions.subject')
                    ),
                }
            },
        ],
        order: [
            [Sequelize.col('QuestionModel.id'), 'desc'],
            [Sequelize.col('maybeDuplicateQuestions.id'), 'desc'],
        ],
    })
  }
}

解决方案 #

Sequelize的关联机制本质上依赖外键映射逻辑，哪怕关闭数据库约束，也需要显式声明一个虚拟外键占位符让框架识别关联关系，否则会因找不到字段映射报错。

修改方案1：添加虚拟外键字段 #

在模型中定义一个不对应实际数据库列的虚拟外键，仅用于框架层面的关联识别：

// AnonymousQuestionModel.ts 修改
@Table
export class AnonymousQuestionModel
  extends Model<AnonymousQuestionAttributes>
  implements AnonymousQuestionAttributes {
    // ... 原有字段
    
    // 虚拟外键，无需在数据库中存在
    @ForeignKey(() => QuestionModel)
    @Column({ type: DataType.VIRTUAL })
    dummyQuestionId?: number

    @BelongsTo(() => QuestionModel, { 
      constraints: false, 
      foreignKeyConstraint: false,
      foreignKey: 'dummyQuestionId' // 指定虚拟外键
    })
    maybeUserQuestion?: QuestionAttributes | null
}

// QuestionModel.ts 修改
@HasMany(() => AnonymousQuestionModel, { 
  constraints: false, 
  foreignKeyConstraint: false,
  foreignKey: 'dummyQuestionId' // 和虚拟外键对应
})
maybeDuplicateQuestions: AnonymousQuestionAttributes[] = []

修改方案2：手动创建关联（无需修改模型字段） #

跳过装饰器定义，在模型初始化完成后手动注册关联：

// 模型加载完成后执行以下代码
QuestionModel.hasMany(AnonymousQuestionModel, {
  as: 'maybeDuplicateQuestions',
  constraints: false,
  foreignKeyConstraint: false,
  foreignKey: 'dummyQuestionId' // 虚拟外键名仅用于框架识别
});

AnonymousQuestionModel.belongsTo(QuestionModel, {
  as: 'maybeUserQuestion',
  constraints: false,
  foreignKeyConstraint: false,
  foreignKey: 'dummyQuestionId'
});

额外优化：查询条件的嵌套字段处理 #

原查询中使用QuestionModel.user.email可能存在歧义，建议改用关联别名简化：

// QuestionsService.ts 修改include部分
include: [
  {
    association: 'user',
    as: 'user' // 显式指定别名
  },
  {
    association: 'maybeDuplicateQuestions',
    on: {
      [Op.and]: [
        Sequelize.where(
          Sequelize.col('user.email'), // 用别名代替全路径
          Op.eq,
          Sequelize.col('maybeDuplicateQuestions.email')
        ),
        Sequelize.where(
          Sequelize.col('QuestionModel.subject'),
          Op.eq,
          Sequelize.col('maybeDuplicateQuestions.subject')
        ),
      ]
    }
  },
]

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