如何使用Sequelize统计同一模型的多关联交易记录

需求说明 #

现有两张表：transaction（交易表）和account（账户表）。一条交易通过id_account（发起账户）和id_account_destination（目标账户）两个外键关联至少1个、最多2个账户。需要统计每个账户作为发起方或目标方关联的所有交易总数，对应的目标SQL如下：

SELECT `account`.`id`, `account`.`name`, COUNT(`transactions`.`id`) AS `transaction_count`
FROM  `accounts` AS `account`
LEFT OUTER JOIN `transactions` AS `transactions`
ON `account`.`id` = `transactions`.`id_account`
OR `account`.`id` = `transactions`.`id_account_destination`
GROUP BY `account`.`id`, `account`.`name`;

模型与关联定义 #

Account模型 #

// account
module.exports = (sequelize, Sequelize) => {
    const DataTypes = Sequelize.DataTypes;
    return sequelize.define("account", {
        id: {
            primaryKey: true,
            type: DataTypes.UUID,
            defaultValue: DataTypes.UUIDV4
        },
        name: {
            type: DataTypes.STRING
        }
    });
};

Transaction模型 #

// transaction
module.exports = (sequelize, Sequelize) => {
    const DataTypes = Sequelize.DataTypes;
    return sequelize.define("transaction", {
        id: {
            primaryKey: true,
            type: DataTypes.UUID,
            defaultValue: DataTypes.UUIDV4
        },
        id_account: {
            type: DataTypes.UUID
        },
        id_account_destination: {
            type: DataTypes.UUID,
            allowNull: true
        },
        amount: {
            type: DataTypes.DOUBLE
        }
    });
};

关联配置 #

db.transaction.belongsTo(db.account, { foreignKey: 'id_account', as: 'account' });
db.transaction.belongsTo(db.account, { foreignKey: 'id_account_destination', as: 'account_destination' });

正确实现方案 #

通过自定义JOIN条件匹配目标SQL逻辑，同时处理重复计数问题：

db.account.findAll({
  subQuery: false,
  attributes: [
    'id',
    'name',
    // 使用DISTINCT避免同一交易因同时关联同一账户（如自转账）被重复计数
    [db.sequelize.fn('COUNT', db.sequelize.fn('DISTINCT', db.sequelize.col('transactions.id'))), 'transaction_count']
  ],
  include: [{
    model: db.transaction,
    attributes: [], // 不需要返回交易字段
    required: false, // 对应LEFT OUTER JOIN，确保无交易的账户也能被返回
    // 自定义JOIN的ON条件，匹配发起账户或目标账户
    on: db.sequelize.or(
      db.sequelize.eq(db.sequelize.col('account.id'), db.sequelize.col('transactions.id_account')),
      db.sequelize.eq(db.sequelize.col('account.id'), db.sequelize.col('transactions.id_account_destination'))
    )
  }],
  // 按账户字段分组，确保所有账户都能被统计
  group: ['account.id', 'account.name']
})

错误尝试分析 #

1. 单关联统计方案：仅统计了id_account关联的交易，未包含id_account_destination的情况，无法满足需求。
2. 报错代码：使用了through参数，该参数仅用于多对多关联的中间表配置，而当前是一对多的belongsTo关联，Sequelize找不到对应的中间表，因此抛出Cannot read properties of undefined (reading 'getTableName')错误。

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