Sequelize：如何在findAll()中添加distinct且不将字段纳入group

原查询代码 #

const counter = await models.CompanyProductionUnitNonCeased
    .findAll({
        raw: true,
        attributes: [
            'production_unit.production_unit_renteds.address.geograpical_administrative_areas.region_code_region.name',
            'production_unit.production_unit_renteds.address.geograpical_administrative_areas.region_code_region.code',
            // [sequelize.literal('DISTINCT `company_id`'), 'company_id', 'company_id'],
            [sequelize.fn('COUNT', 'production_unit.production_unit_renteds.address.geograpical_administrative_areas.region_code_region.code'), 'total']
        ],
        group: ['production_unit.production_unit_renteds.address.geograpical_administrative_areas.region_code_region.name', 'production_unit.production_unit_renteds.address.geograpical_administrative_areas.region_code_region.code'],
        include: [{
            attributes: [],
            required: true,
            model: models.ProductionUnitCore,
            as: "production_unit",
            include: [{
                attributes: [],
                required: true,
                model: models.ProductionUnitRented,
                as: 'production_unit_renteds',
                where: { is_current: true },
                include: [{
                    attributes: [],
                    required: true,
                    model: models.AddressAddress,
                    as: 'address',
                    include: [{
                        attributes: [],
                        required: true,
                        model: models.GeograpicalAdministrativeAreas,
                        as: 'geograpical_administrative_areas',
                        include: [{
                            required: true,
                            model: models.Region,
                            as: 'region_code_region',
                            attributes: [],
                            where: {}
                        }],
                    }]
                }]
            }]
        }]
    })

原执行结果 #

[
  { name: 'Region Sjælland', code: 1085, total: 132696 },
  { name: 'Region Hovedstaden', code: 1084, total: 362711 },
  { name: 'Region Syddanmark', code: 1083, total: 189661 },
  { name: 'Region Midtjylland', code: 1082, total: 224464 },
  { name: 'Region Nordjylland', code: 1081, total: 94023 }
]

需求 #

针对CompanyProductionUnitNonCeased模型的company_id字段添加distinct去重，且不将该字段加入group配置，使total列统计唯一company_id的数量（结果值更小）。

修改后的代码 #

const counter = await models.CompanyProductionUnitNonCeased
    .findAll({
        raw: true,
        attributes: [
            'production_unit.production_unit_renteds.address.geograpical_administrative_areas.region_code_region.name',
            'production_unit.production_unit_renteds.address.geograpical_administrative_areas.region_code_region.code',
            // 核心修改：用COUNT(DISTINCT company_id)统计唯一公司数量
            [sequelize.fn('COUNT', sequelize.literal('DISTINCT `company_id`')), 'total']
        ],
        group: ['production_unit.production_unit_renteds.address.geograpical_administrative_areas.region_code_region.name', 'production_unit.production_unit_renteds.address.geograpical_administrative_areas.region_code_region.code'],
        include: [{
            attributes: [],
            required: true,
            model: models.ProductionUnitCore,
            as: "production_unit",
            include: [{
                attributes: [],
                required: true,
                model: models.ProductionUnitRented,
                as: 'production_unit_renteds',
                where: { is_current: true },
                include: [{
                    attributes: [],
                    required: true,
                    model: models.AddressAddress,
                    as: 'address',
                    include: [{
                        attributes: [],
                        required: true,
                        model: models.GeograpicalAdministrativeAreas,
                        as: 'geograpical_administrative_areas',
                        include: [{
                            required: true,
                            model: models.Region,
                            as: 'region_code_region',
                            attributes: [],
                            where: {}
                        }],
                    }]
                }]
            }]
        }]
    })

修改说明 #

• 核心改动是将attributes中的统计项替换为COUNT(DISTINCT company_id)，通过sequelize.fn结合sequelize.literal实现，直接在聚合函数内部完成去重统计
• 无需修改group配置，因为我们只是在分组后的聚合统计中对company_id去重，不需要将其作为分组维度
• 修改后total列会返回每个区域下的唯一公司数量，数值会比原结果小

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