Sequelize 关联查询的排序问题

近日，在做对话分享时，遇到一个关联查询时的排序问题……

最初，按照直觉，直接在include中定义了order：

typescriptCopy code
1
2
3
4
5
6
7
8
9
include: [
  {
    model: BotMessageModel,
    where: {
      messageStatus: BotMessageStatus.NORMAL
    },
    order: [['messageCreated', 'asc']]
  }
]include: [
  {
    model: BotMessageModel,
    where: {
      messageStatus: BotMessageStatus.NORMAL
    },
    order: [['messageCreated', 'asc']]
  }
]

但发现查询返回的结果是未经排序的，回头看日志，其中的SQL语句并没有order by子句。

起初，没有在意，直接在前端通过array.sort解决（规避）了排序问题，直至今日，才想起来要彻底去解决这个问题。

先是问了ChatGPT，给出了以下的答案：

typescriptCopy code
1
2
3
4
const result = await A.findAll({
  include: [{ model: B }],
  order: [[B, 'b_created', 'ASC']],
});const result = await A.findAll({
  include: [{ model: B }],
  order: [[B, 'b_created', 'ASC']],
});

发现仍然没有返回排序的结果，而是直接报错了：

Unable to find a valid association for model, 'BotMessageModel'

一度以为是model的关联定义出了问题，各种排查，无果。

继续追问，给出了另一个回答：

typescriptCopy code
1
2
3
4
const result = await A.findAll({
  include: [{ model: B }],
  order: [[{ model: B }, 'b_created', 'ASC']],
});const result = await A.findAll({
  include: [{ model: B }],
  order: [[{ model: B }, 'b_created', 'ASC']],
});

然而，结果还是报错：

BotMessageModel is associated to BotSharedRelationModel using an alias. You've included an alias (botMessage), but it does not match the alias(es) defined in your association (message).

但到这里，似乎答案已经很明显了，原来问题出在别名上……😒

改为使用model定义中的alias，果然，问题解决！

以下是完整的例子：

typescriptCopy code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
// BotMessageModel
export class BotMessageModel extends Model {
  ...
  @HasMany(() => BotSharedRelationModel)
  botSharedRelations: BotSharedRelationModel[];
}

// BotSharedRelationModel
export class BotSharedRelationModel extends Model {
  ...
  @ForeignKey(() => BotMessageModel)
  @Column({
    field: 'bot_message_id',
    type: DataType.CHAR(16),
  })
  botMessageId: string;

  @BelongsTo(() => BotMessageModel)
  message: BotMessageModel;
}

// Query
{
  ...
  include: [
    {
      model: BotMessageModel,
      // 必需，且必须和上面的定义一致
      as: 'message'
    }
  ],
  order: [[{ model: BotMessageModel, as: 'message' }, 'messageCreated', 'asc']]
}// BotMessageModel
export class BotMessageModel extends Model {
  ...
  @HasMany(() => BotSharedRelationModel)
  botSharedRelations: BotSharedRelationModel[];
}

// BotSharedRelationModel
export class BotSharedRelationModel extends Model {
  ...
  @ForeignKey(() => BotMessageModel)
  @Column({
    field: 'bot_message_id',
    type: DataType.CHAR(16),
  })
  botMessageId: string;

  @BelongsTo(() => BotMessageModel)
  message: BotMessageModel;
}

// Query
{
  ...
  include: [
    {
      model: BotMessageModel,
      // 必需，且必须和上面的定义一致
      as: 'message'
    }
  ],
  order: [[{ model: BotMessageModel, as: 'message' }, 'messageCreated', 'asc']]
}

最后，回头看了Sequelize的API，才发现Order的定义原来支持这么多种情况！🙃

typescriptCopy code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
type OrderItemAssociation = Association | ModelStatic<Model> | { model: ModelStatic<Model>; as: string } | string
type OrderItemColumn = string | Col | Fn | Literal
export type OrderItem =
  | string
  | Fn
  | Col
  | Literal
  | [OrderItemColumn, string]
  | [OrderItemAssociation, OrderItemColumn]
  | [OrderItemAssociation, OrderItemColumn, string]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemColumn]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemColumn, string]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemColumn]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemColumn, string]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemColumn]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemColumn, string]
export type Order = Fn | Col | Literal | OrderItem[];type OrderItemAssociation = Association | ModelStatic | { model: ModelStatic; as: string } | string
type OrderItemColumn = string | Col | Fn | Literal
export type OrderItem =
  | string
  | Fn
  | Col
  | Literal
  | [OrderItemColumn, string]
  | [OrderItemAssociation, OrderItemColumn]
  | [OrderItemAssociation, OrderItemColumn, string]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemColumn]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemColumn, string]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemColumn]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemColumn, string]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemColumn]
  | [OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemAssociation, OrderItemColumn, string]
export type Order = Fn | Col | Literal | OrderItem[];

附：

API文档参见：https://sequelize.org/docs/v6/advanced-association-concepts/eager-loading/

Node.js

附：