Sequelize关联模型排序问题：按Patient的firstName排序报错

尝试通过Sequelize按患者（Patient）的firstName字段对病例（Case）进行排序时，出现SQL报错：Unknown column 'patient.first_name' in 'order clause'。

现有代码及生成的SQL #

查询代码 #

const cases = await Case.findAll({
    where: {
        diseaseId: disease,
        organizationId: organizations,
    },
    include: [
        {
            model: Patient,
            as: 'patient', 
            where: patientQuery,
            required: true,
        },
        {
            model: CaseField,
        }
    ],
    order: [[{model: Patient, as: 'patient'}, 'firstName', 'ASC']],
    limit: limit,
    offset: (page - 1) * limit
});

生成的SQL #

SELECT 
  `Case`.*, 
  `caseFields`.`id` AS `caseFields.id`, 
  `caseFields`.`case_id` AS `caseFields.caseId`, 
  `caseFields`.`field_name` AS `caseFields.fieldName`, 
  `caseFields`.`field_value` AS `caseFields.fieldValue`, 
  `caseFields`.`field_type` AS `caseFields.fieldType`, 
  `caseFields`.`form_submission_id` AS `caseFields.formSubmissionId`, 
  `caseFields`.`created_at` AS `caseFields.createdAt`, 
  `caseFields`.`updated_at` AS `caseFields.updatedAt`, 
  `caseFields`.`deleted_at` AS `caseFields.deletedAt`, 
  `caseFields`.`case_id` AS `caseFields.case_id` 
FROM 
  (
    SELECT 
      `Case`.`id`, 
      `Case`.`investigation_status` AS `investigationStatus`, 
      `Case`.`status`, 
      `Case`.`assigned_user_id` AS `assignedUserId`, 
      `Case`.`organization_id` AS `organizationId`, 
      `Case`.`disease_id` AS `diseaseId`, 
      `Case`.`patient_id` AS `patientId`, 
      `Case`.`created_at` AS `createdAt`, 
      `Case`.`updated_at` AS `updatedAt`, 
      `Case`.`deleted_at` AS `deletedAt`, 
      `Case`.`patient_id`, 
      `patient`.`id` AS `patient.id`, 
      `patient`.`first_name` AS `patient.firstName`, 
      `patient`.`last_name` AS `patient.lastName`, 
      `patient`.`phone` AS `patient.phone`, 
      `patient`.`email` AS `patient.email`, 
      `patient`.`address` AS `patient.address`, 
      `patient`.`dob` AS `patient.dob`, 
      `patient`.`created_at` AS `patient.createdAt`, 
      `patient`.`updated_at` AS `patient.updatedAt`, 
      `patient`.`deleted_at` AS `patient.deletedAt` 
    FROM 
      `cases` AS `Case` 
      INNER JOIN `patients` AS `patient` ON `Case`.`patient_id` = `patient`.`id` 
    WHERE 
      `Case`.`disease_id` = '48931397-e4a6-48a0-8dcc-f94dc2090daf' 
      AND `Case`.`organization_id` IN (
        'f7828270-f2b7-4a01-ba7b-fffc1b01fcd8'
      ) 
    LIMIT 
      0, 10
  ) AS `Case` 
  LEFT OUTER JOIN `case_fields` AS `caseFields` ON `Case`.`id` = `caseFields`.`case_id` 
ORDER BY 
  `patient`.`first_name` ASC;

模型定义 #

@Table({
    tableName: 'cases',
    timestamps: false, // have to do this for existing columns
})
export class Case extends Model {
    @Column({
        type: DataType.STRING(45),
        primaryKey: true,  
    })
    id!: string;  

    @Column({
        type: DataType.STRING(45),
        field: "investigation_status",
        allowNull: true
    })
    investigationStatus!: string;

    @Column({
        type: DataType.STRING(45),
        field: "status",
        allowNull: false
    })
    status!: string;

    @ForeignKey(() => User)
    @Column({
        type: DataType.STRING(45),
        field: "assigned_user_id",
        allowNull: true
    })
    assignedUserId!: string;

    @ForeignKey(() => Organization)
    @Column({
        type: DataType.STRING(45),
        field: "organization_id",
        allowNull: false
    })
    organizationId!: string;

    @ForeignKey(() => Disease)
    @Column({
        type: DataType.STRING(45),
        field: "disease_id",
        allowNull: false
    })
    diseaseId!: string;

    @ForeignKey(() => Patient)
    @Column({
        type: DataType.STRING(45),
        field: "patient_id",
        allowNull: false
    })
    patientId!: string;
    
    @CreatedAt
    @Column({
        field: 'created_at',
        type: DataType.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('NOW()')
    })
    createdAt!: Date;

    @UpdatedAt
    @Column({
        field: 'updated_at',
        type: DataType.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('NOW()')
    })
    updatedAt!: Date;

    @DeletedAt
    @Column({
        field: 'deleted_at',
        type: DataType.DATE,
        allowNull: true,
    })
    deletedAt!: Date;

    @BelongsTo(() => User, { as: 'assignedUser' })
    assignedUser?: User;

    @BelongsTo(() => Disease)
    disease!: Disease;

    @BelongsTo(() => Patient)
    patient!: Patient;

    @HasMany(() => CaseField, 'case_id')
    caseFields!: CaseField[];
}

@Table({
    tableName: 'patients',
    timestamps: false, // have to do this for existing columns
})
export class Patient extends Model {
    @Column({
        type: DataType.STRING(45),
        primaryKey: true,  
    })
    id!: string;  

    @Column({
        type: DataType.STRING(255),
        field: "first_name",
        allowNull: false
    })
    firstName!: string;

    @Column({
        type: DataType.STRING(255),
        field: "last_name",
        allowNull: false
    })
    lastName!: string;

    @Column({
        type: DataType.STRING(45),
        field: "phone",
        allowNull: true
    })
    phone!: string;

    @Column({
        type: DataType.STRING(255),
        field: "email",
        allowNull: true
    })
    email!: string;

    @Column({
        type: DataType.TEXT,
        field: "address",
        allowNull: true
    })
    address!: string;

    @Column({
        type: DataType.DATE,
        field: "dob",
        allowNull: false
    })
    dob!: Date;
    
    @CreatedAt
    @Column({
        field: 'created_at',
        type: DataType.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('NOW()')
    })
    createdAt!: Date;

    @UpdatedAt
    @Column({
        field: 'updated_at',
        type: DataType.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('NOW()')
    })
    updatedAt!: Date;

    @DeletedAt
    @Column({
        field: 'deleted_at',
        type: DataType.DATE,
        allowNull: true,
    })
    deletedAt!: Date;
    
    @HasMany(() => Case, 'patient_id')
    cases!: Case[];
}

问题原因 #

Sequelize在同时使用limit和包含一对多关联（CaseField是Case的HasMany关联）时，会自动生成嵌套子查询：先在子查询中关联Case和Patient并应用分页，再在外层关联CaseField。但ORDER BY子句被放在了外层，此时外层没有patient表的直接引用，只有子查询返回的别名字段patient.firstName，所以直接引用patient.first_name会导致字段不存在的错误。

解决方案 #

方案1：使用子查询返回的别名排序 #

直接指定子查询中生成的别名patient.firstName作为排序字段，注意需要用Sequelize.literal包裹并添加反引号（因为别名包含小数点）：

const cases = await Case.findAll({
    where: {
        diseaseId: disease,
        organizationId: organizations,
    },
    include: [
        {
            model: Patient,
            as: 'patient', 
            where: patientQuery,
            required: true,
        },
        {
            model: CaseField,
        }
    ],
    order: [[Sequelize.literal('`patient.firstName`'), 'ASC']],
    limit: limit,
    offset: (page - 1) * limit
});

方案2：禁用子查询（Sequelize v6+支持） #

添加subQuery: false选项，让Sequelize生成平级的JOIN语句而非嵌套子查询，这样patient表会直接出现在外层查询中，排序规则可以正常使用关联模型的写法：

const cases = await Case.findAll({
    where: {
        diseaseId: disease,
        organizationId: organizations,
    },
    include: [
        {
            model: Patient,
            as: 'patient', 
            where: patientQuery,
            required: true,
        },
        {
            model: CaseField,
        }
    ],
    order: [[{model: Patient, as: 'patient'}, 'firstName', 'ASC']],
    limit: limit,
    offset: (page - 1) * limit,
    subQuery: false
});

方案对比 #

• 方案1：兼容性强，适用于所有Sequelize版本，但需要手动写字段别名，可读性稍差。
• 方案2：代码更符合Sequelize的关联写法，可读性好，但要求Sequelize版本在v6及以上。

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