Sequelize多关联模型计数结果异常问题求助

同时LEFT JOIN两个与Companies为一对多关联的表时，会产生笛卡尔积。比如1个用户+2个项目，JOIN后会生成2条重复关联记录，直接COUNT会把重复的id统计进去，导致计数结果等于用户数和项目数的乘积。

提供两种实用解决方式：

方式一：用COUNT(DISTINCT)去重统计 #

修改计数逻辑，对关联表的id使用COUNT(DISTINCT)，只统计唯一id的数量，避免笛卡尔积带来的重复计数。

修改后的查询代码：

const companiesInfo = await models.companies.findAll({
    attributes: {
        include: [
            [sequelize.fn("COUNT", sequelize.fn("DISTINCT", sequelize.col("projects.id"))), "projectsCount"],
            [sequelize.fn("COUNT", sequelize.fn("DISTINCT", sequelize.col("app_users.id"))), "usersCount"],
        ],
    },
    include: [
        { model: models.projects, attributes: [] },
        { model: models.app_users, attributes: [] },
    ],
    group: ['companies.id']
})

生成的对应SQL：

SELECT `companies`.`id`, COUNT(DISTINCT `projects`.`id`) AS `projectsCount`, COUNT(DISTINCT `app_users`.`id`) AS `usersCount` FROM `companies` AS `companies` LEFT OUTER JOIN `projects` AS `projects` ON `companies`.`id` = `projects`.`companyId` LEFT OUTER JOIN `app_users` AS `app_users` ON `companies`.`id` = `app_users`.`companyId` GROUP BY `companies`.`id`

方式二：子查询独立计数 #

如果数据量较大，笛卡尔积会影响查询性能，推荐用子查询直接在关联表中统计每个公司的对应数量，避免JOIN产生冗余数据。

修改后的查询代码：

const companiesInfo = await models.companies.findAll({
    attributes: {
        include: [
            [sequelize.literal(`(SELECT COUNT(*) FROM projects WHERE projects.companyId = companies.id)`), "projectsCount"],
            [sequelize.literal(`(SELECT COUNT(*) FROM app_users WHERE app_users.companyId = companies.id)`), "usersCount"],
        ],
    },
    group: ['companies.id']
})

生成的对应SQL：

SELECT `companies`.`id`, (SELECT COUNT(*) FROM projects WHERE projects.companyId = companies.id) AS `projectsCount`, (SELECT COUNT(*) FROM app_users WHERE app_users.companyId = companies.id) AS `usersCount` FROM `companies` AS `companies` GROUP BY `companies`.`id`

两种方式都能得到预期的正确结果：

[
    {name: companyX, usersCount: 1, projectsCount: 2},
    {name: companyY, usersCount: 5, projectsCount: 1},
    ...
]

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