Sequelize关联查询中动态Schema无法传递至关联表的解决方案咨询

使用TypeScript结合Sequelize编写复杂查询，定义了Scope和动态Schema，但关联查询（includes）时，仅主表应用了指定Schema，关联的Profile、Permission等表都没带上Schema前缀。

模型定义 #

@Scopes(() => ({
  ['simpleScope']: simpleScope,
  ['completeScope']: completeScope,
  pagination: paginationModelScope,
}))
@Table({
  createdAt: 'created_at',
  updatedAt: 'updated_at',
  deletedAt: 'deleted_at',
  paranoid: true,
  tableName: 'user',
})
export class User extends Model {
  @PrimaryKey
  @AutoIncrement
  @Column
  id: number;

  // [...] 其他字段

  @ForeignKey(() => Profile)
  @Column({ allowNull: false, type: DataType.INTEGER })
  profile_id: number;

  @BelongsTo(() => Profile)
  declare readonly profile: Profile;
}

@Scopes(() => ({
  ['simpleScope']: simpleScope,
  ['completeScope']: completeScope,
  pagination: paginationModelScope,
}))
@Table({
  createdAt: 'created_at',
  updatedAt: 'updated_at',
  deletedAt: 'deleted_at',
  paranoid: true,
  tableName: 'profile',
})
export class Profile extends Model {
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.BIGINT)
  id: number;

  // [...] 其他字段

  @BelongsToMany(() => Permission, () => ProfilePermission)
  permission?: Permission[];
}

@Scopes(() => ({
  simpleScope: simpleScope,
  pagination: paginationModelScope,
}))
@Table({
  createdAt: 'created_at',
  updatedAt: 'updated_at',
  deletedAt: 'deleted_at',
  paranoid: true,
  tableName: 'permission',
})
export class Permission extends Model {
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.BIGINT)
  id: number;

  // [...] 其他字段

  @ApiProperty({ type: () => Profile, nullable: true, isArray: true })
  @BelongsToMany(() => Profile, () => ProfilePermission)
  profile?: Profile[];
}

Scope定义 #

import { IncludeOptions } from 'sequelize';

export function completeScope(): IncludeOptions {
  return {
    attributes: {
      exclude: ['created_at', 'updated_at', 'deleted_at', 'senha'],
      include: [],
    },
    include: [
      {
        association: 'profile',
        required: true,
        paranoid: true,
        attributes: {
          exclude: ['created_at', 'updated_at', 'deleted_at'],
          include: [],
        },
        include: [
          {
            association: 'permission',
            required: true,
            paranoid: false,
            attributes: {
              exclude: ['created_at', 'updated_at', 'deleted_at'],
              include: [],
            },
          },
        ],
      },
    ],
  };
}

查询代码 #

public static async findOne<Response = any>(model, id, scope: any = 'completeScope', schema: string): Promise<Response> {
  const response = await model.schema(schema).scope([scope]).findOne({ where: { id } });

  if (response === null) {
    throw new NotFoundException('Not found!');
  }

  return response;
}

使用示例 #

async findOne(id, scope, schema) {
  return BasicCrud.findOne(this.UserModel, id, scope, schema);
}

生成的SQL #

Executed (default): SELECT "User"."id", "User"."email", "User"."profile_id", "profile"."id" AS "profile.id", "profile"."name" AS "profile.name", "profile->permission"."id" AS "profile.permission.id", "profile->permission"."name" AS "profile.permission.name", "profile->permission->ProfilePermission"."profile_id" AS "profile.permission.ProfilePermission.profile_id", "profile->permission->ProfilePermission"."permission_id" AS "profile.permission.ProfilePermission.permission_id",
 "profile->permission->ProfilePermission"."created_at" AS "profile.permission.ProfilePermission.created_at", "profile->permission->ProfilePermission"."updated_at" AS "profile.permission.ProfilePermission.updated_at" FROM "SCHEMA"."User" AS "User" INNER JOIN "profile" AS "profile" ON "User"."profile_id" = "profile"."id" AND ("profile"."deleted_at" IS NULL) INNER JOIN ( "profile_permission" AS "profile->permission->ProfilePermission" INNER JOIN "permission" AS "profile->permission" ON "profile->permission"."id" = "profile->permission->ProfilePermission"."permission_id") ON "profile"."id" = "profile->permission->ProfilePermission"."profile_id" WHERE ("User"."deleted_at" IS NULL AND "User"."id" = 1);

可以看到，只有主表User带有指定的SCHEMA前缀，关联的profile、permission等表都没有应用该Schema。

方法一：修改Scope函数，接收Schema参数并指定关联模型的Schema版本 #

将Scope函数改为可传入schema参数，在include配置中显式指定关联模型为对应Schema的实例：

import { IncludeOptions } from 'sequelize';
import { Profile, Permission } from './models'; // 导入关联模型

export function completeScope(schema: string): IncludeOptions {
  return {
    attributes: {
      exclude: ['created_at', 'updated_at', 'deleted_at', 'senha'],
      include: [],
    },
    include: [
      {
        association: 'profile',
        model: Profile.schema(schema), // 指定带Schema的Profile模型
        required: true,
        paranoid: true,
        attributes: {
          exclude: ['created_at', 'updated_at', 'deleted_at'],
          include: [],
        },
        include: [
          {
            association: 'permission',
            model: Permission.schema(schema), // 指定带Schema的Permission模型
            required: true,
            paranoid: false,
            attributes: {
              exclude: ['created_at', 'updated_at', 'deleted_at'],
              include: [],
            },
          },
        ],
      },
    ],
  };
}

然后修改查询函数，调用Scope时传入schema：

public static async findOne<Response = any>(model, id, scope: any = 'completeScope', schema: string): Promise<Response> {
  // 根据scope名称调用对应函数并传入schema
  const scopeOptions = typeof scope === 'string' 
    ? { [scope]: () => completeScope(schema) } 
    : scope;
  
  const response = await model.schema(schema).scope(scopeOptions).findOne({ where: { id } });

  if (response === null) {
    throw new NotFoundException('Not found!');
  }

  return response;
}

方法二：在查询时动态修改Scope的include配置 #

如果不想修改原Scope函数，可以在查询前动态遍历Scope的include选项，为每个关联模型添加Schema：

import { Includeable } from 'sequelize';

// 递归函数：为所有关联模型添加Schema
function applySchemaToIncludes(includes: Includeable[], schema: string) {
  if (!Array.isArray(includes)) includes = [includes];
  
  includes.forEach(include => {
    if ('model' in include && include.model) {
      include.model = include.model.schema(schema);
    }
    if (include.include) {
      applySchemaToIncludes(include.include, schema);
    }
  });
}

public static async findOne<Response = any>(model, id, scopeName: string = 'completeScope', schema: string): Promise<Response> {
  // 获取模型的Scope配置
  const scopeConfig = model.options.scopes[scopeName];
  // 克隆Scope配置避免修改原定义
  const scopeOptions = JSON.parse(JSON.stringify(scopeConfig));
  
  // 为所有关联模型应用Schema
  if (scopeOptions.include) {
    applySchemaToIncludes(scopeOptions.include, schema);
  }

  const response = await model.schema(schema).scope({ [scopeName]: scopeOptions }).findOne({ where: { id } });

  if (response === null) {
    throw new NotFoundException('Not found!');
  }

  return response;
}

这两种方法都能让关联表在查询时带上指定的Schema前缀，解决主表与关联表Schema不一致的问题。

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