Sequelize查询PostgreSQL：拆分同ApplicationId的关联共享记录

数据库表结构 #

shareData表 #

application表 #

需求说明 #

获取与指定userId相关的所有应用数据，需覆盖三种场景：

• 用户是application表的authorId（创建但未分享的应用）
• 用户是shareData表的senderId（发起分享的用户）
• 用户存在于shareData表recipients字段的userId中（被分享的用户）

要求同一ApplicationId对应的每条共享记录拆分为独立条目，而非合并成一条。

当前问题 #

现有Sequelize查询返回结果中，同一ApplicationId的多条共享数据被合并，示例如下：

{
    "data": [
        {
            "ApplicationId": 1,
            // ...其他应用内容
            "recipients": [
                { "userId": 3 },
                { "userId": 2 }
            ],
            "roomId": "81cc7f78-785a-4340-830c-564469bd62ef, 6e25c3c3-6389-43ae-8be5-0ff091a828fd",
            "User": { /*...用户数据*/ }
        }
        //...其他记录
    ]
}

期望结果 #

需将合并记录拆分为独立条目，示例如下：

{
    "data": [
        {
            "ApplicationId": 1,
            //...其他应用内容
            "recipients": [ { "userId": 2 } ],
            "roomId": "6e25c3c3-6389-43ae-8be5-0ff091a828fd",
            "User": { /*...用户数据*/ }
        },
        {
            "ApplicationId": 1,
            //...其他应用内容
            "recipients": [ { "userId": 3 } ],
            "roomId": "81cc7f78-785a-4340-830c-564469bd62ef",
            "User": { /*...用户数据*/ }
        }
        //...其他记录
    ]
}

解决方案 #

方案一：修改Sequelize查询，直接返回拆分结果 #

问题根源是查询中可能使用了聚合函数或GROUP BY，导致同一应用的多条共享数据被合并。调整查询逻辑，去掉聚合操作，让每条共享记录对应独立的应用条目：

const { Op } = require('sequelize');

const sharedData = await Application.findAll({
    attributes: [
        'id', 'authorId', 'applicationContent'
    ],
    where: {
        [Op.or]: [
            { authorId: userId },
            sequelize.literal(`EXISTS (
                SELECT 1 FROM "shareData" 
                WHERE "shareData"."applicationId" = "Application"."id" 
                AND "shareData"."recipients" @> '[{"userId": ${userId}}]'
            )`)
        ]
    },
    include: [
        {
            model: Share,
            as: 'shares',
            attributes: ['recipients', 'roomId', 'senderId'],
            where: {
                [Op.or]: [
                    { senderId: userId },
                    sequelize.literal(`"shares"."recipients" @> '[{"userId": ${userId}}]'`)
                ]
            },
            required: false,
            separate: true
        },
        {
            model: User,
            attributes: [/* 所需用户字段，比如'id', 'name' */],
            as: 'author'
        }
    ],
    raw: false,
    nest: true
});

// 格式化结果，拆分共享记录
const formattedData = sharedData.flatMap(app => {
    if (!app.shares || app.shares.length === 0) {
        return [{
            ApplicationId: app.id,
            authorId: app.authorId,
            applicationContent: app.applicationContent,
            recipients: [],
            roomId: null,
            User: app.author
        }];
    }
    return app.shares.map(share => ({
        ApplicationId: app.id,
        authorId: app.authorId,
        applicationContent: app.applicationContent,
        recipients: JSON.parse(share.recipients),
        roomId: share.roomId,
        User: app.author
    }));
});

return { data: formattedData };

方案二：查询后手动拆分合并数据 #

如果无法修改原始查询，可在获取结果后手动拆分合并字段：

// 假设originalData是原始查询返回的data数组
const originalData = /* 你的原始查询结果.data */;

const formattedData = originalData.flatMap(item => {
    if (!item.roomId || item.roomId.split(',').length === 0) {
        return [{ ...item, recipients: [], roomId: null }];
    }
    const roomIds = item.roomId.split(',').map(id => id.trim());
    const recipientsList = item.recipients;
    return roomIds.map((roomId, index) => ({
        ...item,
        recipients: [recipientsList[index]],
        roomId: roomId
    }));
});

return { data: formattedData };

关键说明 #

• 方案一中的separate: true会让Sequelize对关联表执行独立查询，避免主查询与关联查询合并导致的结果聚合。
• 未分享的应用（仅authorId匹配）需单独处理，补充空的共享字段。
• 方案二需确保合并的roomId字符串与recipients数组的顺序严格对应，否则会出现数据匹配错误。

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