Sequelize.js关联表：同时统计成员数与获取关联属性的方法

可以通过两种方式实现同时获取项目成员数量和ProjectMember表的指定属性：

方法一：利用数据库聚合函数汇总关联属性 #

根据你使用的数据库类型，用聚合函数把需要的ProjectMember属性打包成集合，和成员统计数一起返回。

PostgreSQL版本代码示例 #

project = await Project.findAll({
    where: {
        [Op.and]: [
            { status: { [Op.ne]: 99 } },
            { '$workspace.company_id$': { [Op.eq]: companyId } },
        ]
    },
    attributes: {
        include: [
            [sequelize.fn("COUNT", sequelize.col("projectMember.user_id")), "memberCount"],
            // 聚合成员ID列表
            [sequelize.fn("array_agg", sequelize.fn("DISTINCT", sequelize.col("projectMember.user_id"))), "memberUserIds"],
            // 聚合成员详细信息（比如ID和角色）为JSON数组
            [sequelize.fn("array_agg", sequelize.json_build_object(
                'userId', sequelize.col('projectMember.user_id'),
                'role', sequelize.col('projectMember.role')
            )), "memberDetails"]
        ]
    },
    include: [
        {
            model: ProjectMember,
            as: 'projectMember',
            required: false,
            attributes: []
        },
        {
            model: Workspace,
            as: 'workspace',
            attributes: []
        }
    ],
    group: ['project_master.id']
})

MySQL版本代码示例 #

把PostgreSQL的array_agg换成GROUP_CONCAT：

project = await Project.findAll({
    where: {
        [Op.and]: [
            { status: { [Op.ne]: 99 } },
            { '$workspace.company_id$': { [Op.eq]: companyId } },
        ]
    },
    attributes: {
        include: [
            [sequelize.fn("COUNT", sequelize.col("projectMember.user_id")), "memberCount"],
            // 聚合成员ID列表
            [sequelize.fn("GROUP_CONCAT", sequelize.fn("DISTINCT", sequelize.col("projectMember.user_id"))), "memberUserIds"],
            // 聚合成员ID+角色的字符串，后续可自行解析为对象
            [sequelize.fn("GROUP_CONCAT", sequelize.concat(
                sequelize.col('projectMember.user_id'), 
                ':', 
                sequelize.col('projectMember.role')
            )), "memberDetails"]
        ]
    },
    include: [
        {
            model: ProjectMember,
            as: 'projectMember',
            required: false,
            attributes: []
        },
        {
            model: Workspace,
            as: 'workspace',
            attributes: []
        }
    ],
    group: ['project_master.id']
})

这种方式的优势是一次查询搞定，但需要适配数据库的聚合语法，返回的关联属性是聚合后的集合。

方法二：分两次查询关联数据（更灵活） #

先查项目和成员数，再批量拉取所有关联的ProjectMember属性，最后手动关联到对应项目上：

// 1. 查询项目列表及成员统计数
const projects = await Project.findAll({
    where: {
        [Op.and]: [
            { status: { [Op.ne]: 99 } },
            { '$workspace.company_id$': { [Op.eq]: companyId } },
        ]
    },
    attributes: {
        include: [
            [sequelize.fn("COUNT", sequelize.col("projectMember.user_id")), "memberCount"],
        ]
    },
    include: [
        {
            model: ProjectMember,
            as: 'projectMember',
            required: false,
            attributes: []
        },
        {
            model: Workspace,
            as: 'workspace',
            attributes: []
        }
    ],
    group: ['project_master.id'],
    raw: false // 保留模型实例，方便后续处理
})

// 2. 批量获取所有项目对应的成员属性
const projectIds = projects.map(p => p.id)
const projectMembers = await ProjectMember.findAll({
    where: { project_id: projectIds },
    attributes: ['project_id', 'user_id', 'role'] // 指定需要的属性
})

// 3. 将成员属性关联到对应项目
projects.forEach(project => {
    project.members = projectMembers.filter(pm => pm.project_id === project.id)
})

这种方式不需要依赖数据库特定语法，返回的成员属性是结构化的数组，后续处理更灵活，适合需要对成员数据做复杂操作的场景。

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