Sequelize-Typescript中Order By失效，提示无法找到cardStep关联

问题描述 #

我是NestJS和Sequelize-Typescript新手，查询时不加order属性能正常返回结果，但添加order后触发错误：

Error: Unable to find a valid association for model,'cardStep'

以下是相关代码和无排序时的输出：

服务层查询函数（原报错版本） #

async fetchSubReports(id:string):Promise<any>{
    try {
        const report = await this.cardRepository.findAll<Card>({
            include:[{
                model:cardStep,
                attributes: {
                    exclude: ['createdAt','updatedAt','cardId']
                },
                include:[{
                    model:Counter,
                    attributes: {
                        exclude: ['createdAt','updatedAt','cardId','stepId']
                    },
                }]
            }],
            where: {id},
            order: [[ cardStep,'order','DESC']],                
        });
        return report;
    } catch (err) {
        console.log({err})
        return {};
    }
}

cardStep模型 #

import {
    Table,
    Column,
    Model,
    PrimaryKey,
    ForeignKey,
    DataType,
    BelongsTo,
    HasOne,
} from 'sequelize-typescript';

import { Card } from 'src/card/card.entity';
import { Counter } from 'src/counter/counter.entity';

@Table({
    tableName: 'card_step',
    timestamps: true,
})

export class cardStep extends Model {
    @PrimaryKey
    @Column({
        type: DataType.UUID,
        defaultValue: DataType.UUIDV4,
    })
    id: string

    @Column({ allowNull: false })
    stepTitle: string

    @PrimaryKey
    @Column({ allowNull: false ,unique: 'unique_order'})
    order: number

    @Column({ allowNull: false })
    draft: boolean

    @Column({ allowNull: false })
    time: string

    @Column({ allowNull: false })
    date: string

    @PrimaryKey
    @ForeignKey(() => Card)
    @Column({ type: DataType.UUID ,unique: 'unique_order'})
    cardId: string

    @BelongsTo(() => Card)
    reports: Card

    @HasOne(()=>Counter)
    counters: Counter[]
}

Card模型 #

import {
    Table,
    Column,
    Model,
    DataType,
    PrimaryKey,
    ForeignKey,
    BelongsTo,
    HasMany,
} from 'sequelize-typescript';

import { Client } from '../client/client.entity';
import { cardStep } from 'src/card-step/card-step.entity';
import { Counter } from 'src/counter/counter.entity';

@Table({
    tableName: 'cards',
    timestamps: true,
})

export class Card extends Model {
    @PrimaryKey
    @Column({
        type: DataType.UUID,
        defaultValue: DataType.UUIDV4,
    })
    id: string

    @Column({ allowNull: false })
    title: string

    @Column({ allowNull: false })
    sendTo: string

    @Column({ allowNull: false })
    sendAnswersTo: string

    @Column({ allowNull: false })
    startDate: string

    @Column({ allowNull: false })
    endDate: string

    @ForeignKey(() => Client)
    @Column({ type: DataType.UUID })
    clientId: string

    @BelongsTo(() => Client)
    user: Client

    @HasMany(() => cardStep)
    subReports: cardStep[]

    @HasMany(() => Counter)
    counters: Counter[]
}

无Order By时的示例输出 #

[
  {
    "id": "cd3bd45c-69d6-4d1a-84b2-057a19bc19a8",
    "title": "diwali",
    "sendTo": "hemant soni",
    "sendAnswersTo": "Guys let's celebrate the party",
    "startDate": "01-01-203",
    "endDate": "02-01-2023",
    "clientId": "b995c472-733b-404f-879f-d7b7c970f2c1",
    "createdAt": "2022-11-01T06:14:26.438Z",
    "updatedAt": "2022-11-01T06:14:26.438Z",
    "subReports": [
      {
        "id": "fccc8c82-2f28-4911-b5ed-859d196ac585",
        "stepTitle": "Adults and childern",
        "order": 0,
        "draft": true,
        "time": "06:55",
        "date": "01-01-2023",
        "counters": {
          "id": "0348ab0a-da77-4cdd-995f-4e9f20d1ba6f",
          "attendees": null,
          "children": 10,
          "adults": 50,
          "boys": null,
          "girls": null,
          "over18": null,
          "under18": null,
          "over60": null
        }
      },
      {
        "id": "61a78423-0093-4e43-bdb3-5e9b4ac35211",
        "stepTitle": "Adults and childern",
        "order": 1,
        "draft": true,
        "time": "06:55",
        "date": "01-01-2023",
        "counters": {
          "id": "3da1dffc-48ed-482a-9a84-9dfcb77aaa39",
          "attendees": null,
          "children": 10,
          "adults": 50,
          "boys": null,
          "girls": null,
          "over18": null,
          "under18": null,
          "over60": null
        }
      },
      {
        "id": "6ccf73d2-d2d9-405f-be9e-e9c16fab6b44",
        "stepTitle": "Adults and childern",
        "order": 5,
        "draft": true,
        "time": "06:55",
        "date": "01-01-2023",
        "counters": {
          "id": "5723b054-822f-457d-93cf-384e47684a76",
          "attendees": null,
          "children": 10,
          "adults": 50,
          "boys": null,
          "girls": null,
          "over18": null,
          "under18": null,
          "over60": null
        }
      }
    ]
  }
]

解决方法 #

核心原因 #

你在order中直接使用了cardStep模型类，但Sequelize需要通过Card模型中定义的关联名称来识别要排序的关联数据，而不是模型类本身。在Card模型里，cardStep的关联名称是subReports（对应@HasMany(() => cardStep) subReports: cardStep[]）。

修正方案1：修改order选项的写法 #

把order中的cardStep替换成关联名称subReports：

order: [[ 'subReports', 'order', 'DESC' ]]

修正方案2：在关联内部添加排序（更精准） #

如果只想对关联的cardStep数据排序，也可以直接在include的cardStep配置里加order：

include:[{
    model:cardStep,
    attributes: {
        exclude: ['createdAt','updatedAt','cardId']
    },
    include:[{
        model:Counter,
        attributes: {
            exclude: ['createdAt','updatedAt','cardId','stepId']
        },
    }],
    order: [['order', 'DESC']] // 仅对当前关联的cardStep排序
}]

修改后的完整服务层代码 #

async fetchSubReports(id:string):Promise<any>{
    try {
        const report = await this.cardRepository.findAll<Card>({
            include:[{
                model:cardStep,
                attributes: {
                    exclude: ['createdAt','updatedAt','cardId']
                },
                include:[{
                    model:Counter,
                    attributes: {
                        exclude: ['createdAt','updatedAt','cardId','stepId']
                    },
                }]
            }],
            where: {id},
            order: [[ 'subReports', 'order', 'DESC' ]],                
        });
        return report;
    } catch (err) {
        console.log({err})
        return {};
    }
}

额外优化建议（非报错相关） #

cardStep模型中@HasOne(()=>Counter)应该改成@HasMany，因为你定义的counters是数组类型，这样和实际关联关系更匹配。

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