Sequelize-Typescript一对多关联查询仅返回单个子实体问题

问题背景 #

使用Sequelize-Typescript开发Express REST API，定义了Album（相册）和Photo（照片）的一对多关联（一个相册对应多张照片）。创建Album时可成功关联多条Photo并写入数据库，但调用getAlbumById查询时，仅返回1条Photo实体，数据库中实际存在3条关联数据。

相关代码片段 #

Album.ts #

@Table({
  timestamps: true,
  deletedAt: "albumDeletedAt",
  paranoid: true,
  createdAt: true,
  updatedAt: true
})
export class Album extends Model {
  @PrimaryKey
  @Column({
      type: DataType.INTEGER,
      primaryKey: true,
      autoIncrement: true
  })
  declare id: number;

  @Column({
      type: DataType.STRING,
      allowNull: false
  })
  declare name: string;

  @Column({
     type: DataType.STRING,
     allowNull: true
  })
  declare description?: string;

  @Column({
      type: DataType.STRING,
      allowNull: false
  })
  declare thumbnailURL: string;

  @HasMany(() => Photo)
  declare photos: Photo[];

  @CreatedAt
  declare createdAt: Date;

  @UpdatedAt
  declare updatedAt: Date;
 }

Photo.ts #

import { BelongsTo, Column, CreatedAt, DataType, ForeignKey, Model, PrimaryKey, Table, UpdatedAt } from "sequelize-typescript";
import { Album } from "./album";

@Table({
  timestamps: true,
  deletedAt: "photoDeletedAt",
  paranoid: true,
  createdAt: true,
  updatedAt: true
})
export class Photo extends Model {
  @PrimaryKey
  @Column({
      type: DataType.INTEGER,
      primaryKey: true,
      autoIncrement: true
  })
  declare id: number;

  @Column({
     type: DataType.STRING,
     allowNull: false
  })
  declare name: string;

  @Column({
     type: DataType.STRING,
     allowNull: false
  })
  declare photoURL: string;

  @ForeignKey(() => Album)
  @Column({
     type: DataType.INTEGER,
     allowNull: true,
  })
  declare albumId: number;

  @BelongsTo(() => Album)
  declare album: Album;

  @CreatedAt
  declare createdAt: Date;

  @UpdatedAt
  declare updatedAt: Date;
}

AlbumRepository.ts #

export class AlbumRepository {
private albumRepository: Repository<Album>;
private photoRepository: Repository<Photo>;

constructor(public databaseMiddleware: DatabaseMiddleware) {
    this.albumRepository = databaseMiddleware.getDatabaseInstance().getRepository(Album);
    this.photoRepository = databaseMiddleware.getDatabaseInstance().getRepository(Photo);
}

public async createAlbum(albumCreateDTO: AlbumCreateDto) {
    try {
        return await this.albumRepository.create({ ...albumCreateDTO }, { include: [this.photoRepository] });
    } catch (error) {
        console.error("向数据库创建Album数据时出错：", error?.message);
        return null;
    }
}

public async getAlbumById(id: number) : Promise<Album | null> {
    try {
        return await this.albumRepository.findByPk(id, { include: [this.photoRepository] }) ?? null;
    } catch (error) {
        console.error(`通过ID查询Album出错，ID：${id}`, error);
        return null;
    }
}
}

数据库配置（DatabaseMiddleware.ts） #

export default class DatabaseMiddleware {
private databaseInstance: Sequelize;

constructor() {
    this.databaseInstance = new Sequelize({
        dialect: "mysql",
        host: process.env.DATABASE_HOST,
        database: process.env.DATABASE_NAME,
        username: process.env.DATABASE_USERNAME,
        password: process.env.DATABASE_PASSWORD,
        port: 3306,
        logging(sql, timing) {
            console.log("Sequelize执行的SQL语句：", sql, timing);
        },
        query: { raw: true },
        models: [User, Album, Video, Photo],
        repositoryMode: true
    });
}

public async connectToDatabase() {
    try {
        await this.databaseInstance.authenticate();
        if (process.env.ENVIRONMENT_PROFILE === "development") {
            await this.databaseInstance.sync({ alter: true, force: true });
        }
        console.log("数据库连接成功建立");
    } catch (error) {
        console.error("无法连接数据库：", error);
    }
}

public getDatabaseInstance() {
    return this.databaseInstance;
}
}

问题更新 #

调用getAlbumById时执行的SQL能正确关联查询，但返回结果仅包含1条Photo；尝试移除全局raw: true配置后，出现报错：

Error finding the album via ID for the ID of 1 due to TypeError: result.get is not a function
at E:\Coding Projects\Projects\Back-Ends\Profilic\node_modules\sequelize\src\model.js:1885:39
at Array.reduce ()
at E:\Coding Projects\Projects\Back-Ends\Profilic\node_modules\sequelize\src\model.js:1884:19
at Array.map ()
at Function._findSeparate (E:\Coding Projects\Projects\Back-Ends\Profilic\node_modules\sequelize\src\model.js:1881:39)
at Function.findAll (E:\Coding Projects\Projects\Back-Ends\Profilic\node_modules\sequelize\src\model.js:1840:24)
at processTicksAndRejections (node:internal/process/task_queues:95:5)
at async Function.findOne (E:\Coding Projects\Projects\Back-Ends\Profilic\node_modules\sequelize\src\model.js:1998:12)
at async Function.findByPk (E:\Coding Projects\Projects\Back-Ends\Profilic\node_modules\sequelize\src\model.js:1955:12)
at async AlbumRepository.getAlbumById (E:\Coding Projects\Projects\Back-Ends\Profilic\repositories\album.repository.ts:52:20)

解决方案 #

核心问题分析 #

1. 全局raw: true配置：该配置会让所有查询返回平展的原始数据，一对多关联查询时，多条Photo数据会因主表（Album）数据重复而被最后一条覆盖，导致仅返回1条Photo。
2. Include使用Repository而非Model：查询时传入Repository实例而非Model类，Sequelize无法正确识别关联元数据，导致关联处理异常。

具体修复步骤 #

1. 移除全局raw: true配置

修改DatabaseMiddleware.ts中的Sequelize初始化代码，删除全局query: { raw: true }：

constructor() {
    this.databaseInstance = new Sequelize({
        dialect: "mysql",
        host: process.env.DATABASE_HOST,
        database: process.env.DATABASE_NAME,
        username: process.env.DATABASE_USERNAME,
        password: process.env.DATABASE_PASSWORD,
        port: 3306,
        logging(sql, timing) {
            console.log("Sequelize执行的SQL语句：", sql, timing);
        },
        // 移除全局raw配置
        models: [User, Album, Video, Photo],
        repositoryMode: true
    });
}

2. 调整查询的Include配置

在AlbumRepository.ts的getAlbumById方法中，直接使用Photo模型类而非Repository实例：

// 先确保导入Photo模型
import { Photo } from "../models/Photo";

public async getAlbumById(id: number) : Promise<Album | null> {
    try {
        return await this.albumRepository.findByPk(id, { include: [Photo] }) ?? null;
    } catch (error) {
        console.error(`通过ID查询Album出错，ID：${id}`, error);
        return null;
    }
}

3. 若需返回原始数据格式

如果业务需要返回平展的原始对象而非Sequelize Model实例，可在单个查询中单独设置raw: true并配合nest: true，确保关联数据正确嵌套：

public async getAlbumById(id: number) : Promise<any | null> {
    try {
        return await this.albumRepository.findByPk(id, { 
            include: [Photo],
            raw: true,
            nest: true // 启用嵌套，将photos作为数组存入Album对象
        }) ?? null;
    } catch (error) {
        console.error(`通过ID查询Album出错，ID：${id}`, error);
        return null;
    }
}

修复原理 #

• 移除全局raw: true后，Sequelize返回Model实例，会自动处理一对多关联，将所有关联的Photo存入photos数组。
• 使用Model类进行Include，Sequelize能正确读取关联元数据，构建正确的关联查询逻辑。
• nest: true配合raw: true时，Sequelize会将关联数据嵌套到对应的属性中，避免平展数据导致的覆盖问题。

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