如何在Sequelize.js中通过不同自定义外键两次关联同一模型查询数据

需求 #

我有一个基于Discord和Telegram的聊天机器人项目，已将两个平台的用户数据及用户发送的消息存储到PostgreSQL数据库中。需要实现：通过用户发送的消息内容搜索匹配的用户，并统计符合条件的用户数量。例如：

• Discord用户A发送过消息“hello world”
• Telegram用户B发送过消息“hello world”
  当搜索关键词“hello”时，应获取用户A和B的完整数据，统计数为2。

技术栈 #

• Node.js: 16.14.2
• Sequelize.js: 5.22.4
• PostgreSQL: 14.5

数据库表结构 #

Users表 #

表定义及关联关系：

const Users = sequelize.define("Users", {
  name: DataTypes.TEXT,
  userId: DataTypes.TEXT
})
Users.hasMany(models.Messages, {
  as: 'discordMessages',
  sourceKey: 'userId',
  foreignKey: 'discordUserId'
})
Users.hasMany(models.Messages, {
  as: 'telegramMessages',
  sourceKey: 'userId',
  foreignKey: 'telegramUserId'
})

通过userId区分用户所属平台，示例数据：

name | userId
-------------
A    | 123abc
-------------
B    | 456def
-------------
C    | 789ghi

其中A是Discord用户，B、C是Telegram用户。

Messages表 #

表定义及关联关系：

const Messages = sequelize.define("Messages", {
  message: DataTypes.TEXT,
  discordUserId: DataTypes.TEXT,
  telegramUserId: DataTypes.TEXT
})
Messages.belongsTo(models.Users, {
  as: 'discordMessages',
  targetKey: 'userId',
  foreignKey: 'discordUserId'
})
Messages.belongsTo(models.Users, {
  as: 'telegramMessages',
  targetKey: 'userId',
  foreignKey: 'telegramUserId'
})

关联规则：一个用户拥有多条消息，一条消息仅属于一个用户（每条消息的discordUserId和telegramUserId只有一个有值）。示例数据：

message        | discordUserId | telegramUserId
------------------------------------------------
hello world    | 123abc        |
------------------------------------------------
hello world    |               | 456def
------------------------------------------------
sorry.         | 123abc        |
------------------------------------------------
thank you.     |               | 456def
------------------------------------------------
I'm happy      |               | 789ghi

已尝试的代码及问题 #

最接近需求的版本 #

const { count, rows } = await Users.findAndCountAll({
    where: {
      [Op.or]: [
        { '$discordMessages.message$': { [Op.iLike]: `%${search}%` } },
        { '$telegramMessages.message$': { [Op.iLike]: `%${search}%` } }
      ]
    },
    include: [
      { model: Messages, as: 'discordMessages' },
      { model: Messages, as: 'telegramMessages' }
    ],
    subQuery: false,
    distinct: true
})

问题：搜索“hello”时，count返回2，但rows中仅包含用户A，未返回用户B，实际结果：

count: 2
rows: [
    Users: {
      name: 'A',
      userId: '123abc'
    }
]

其他尝试版本 #

版本1：

const { count, rows } = await Users.findAndCountAll({
    where: {
      [Op.or]: [
        { '$discordMessages.message$': { [Op.iLike]: `%${search}%` } },
        { '$telegramMessages.message$': { [Op.iLike]: `%${search}%` } }
      ]
    },
    include: [
      { model: Messages, as: 'discordMessages' },
      { model: Messages, as: 'telegramMessages' }
    ],
    subQuery: false,
    distinct: true,
    required: true,
    distinct: true
})

版本2：

const { count, rows } = await Users.findAndCountAll({
    include: [
      {
        model: Messages,
        as: 'discordMessages',
        where: {
          message: { [Op.iLike]: `%${search}%` }
        },
        separate: true
      },
      {
        model: Messages,
        as: 'telegramMessages',
        where: {
          message: { [Op.iLike]: `%${search}%` }
        },
        separate: true
      }
    ],
    subQuery: false,
    distinct: true
})

期望结果 #

搜索“hello”后，应返回2条符合条件的用户数据，统计数为2：

count: 2
rows: [
    Users: {
      name: 'A',
      userId: '123abc'
    },
    Users: {
      name: 'B',
      userId: '456def'
    }
]

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