基于customer_id关联三表，Sequelize查询返回嵌套媒体数据问题

表结构 #

deals                     deals_media                     access_to_deals
name     pic_url          type   media_url  deal_id       customer_id       deal_id
deal1    http://blah      video  url.       1             1                 1
deal2    http://blah2     img    url2.      1             1                 2
deal3    http://blah3     video  url3.      2             2                 1

需求 #

发送带有customer_id查询参数的GET API请求，返回该客户可访问的所有deals数据，且每个deal对象中嵌套对应的deals_media数组。

当前实现代码 #

sync (req, res) => {
    try {
        const customerId = req.query.customer_id;
        let deals = await db.sequelize.query(
            'SELECT DISTINCT deals.* FROM deals \n' +
            'JOIN access ON deals.id = access.deals_id AND access.customerId=$1 \n' +
            'LEFT JOIN media ON deals.id = media.deals_id \n',
            { bind: [customerId], type: 'RAW' },
        );
        res.send(campaign[0]);
    } catch (err) {...}
};

当前问题 #

返回的响应是重复的deal条目，每条对应一条媒体数据：

[
    {
       "id":1,
       "deal_name": "deal1",
       "other data": "data",
       "media type": "video",
       "media URL": "https://www.......",
       "deals_id": 1
    },
    {
       "id":2,
       "deal_name": "deal1",
       "other data": "data",
       "media type": "picture",
       "media URL": "https://www.......",
       "deals_id": 1
    },
    {
       "id":3,
       "deal_name": "deal1",
       "other data": "data",
       "media type": "audio",
       "media URL": "https://www.......",
       "deals_id": 1
    }
]

期望响应格式 #

每个deal嵌套对应的媒体数组：

[
    {
       "id":1,
       "deal_name": "deal1",
       "other data": "data",
       "media": [
           {
                "media type": "video",
                "media URL": "https://www......."
           },
           {
                "media type": "picture",
                "media URL": "https://www......."
           },
           {
                "media type": "audio",
                "media URL": "https://www......."
           }
       ]
    }
]

尝试过的方案及问题 #

• 尝试在最后一个JOIN后添加GROUP BY media.deals_id，但报错要求将deals.id和media.id加入GROUP BY，且结果仍不符合预期。
• 按照建议修改为JSON构建的查询后，出现错误Error is Deals: SequelizeDatabaseError: schema "deals" does not exist，修改后的查询代码：

SELECT json_build_object ( 
    'id', deals.id 
    'deals_name', deals.deals_name 
    'deals_icon_url', deals.deals_icon_url 
    'conversion_event', deals.conversion_event 
    'created_at', daels.created_at 
    'updated_at', deals.updated_at 
    'deleted_at', deals.deleted_at 
    'media', json_build_array ( 
        json_build_objecty ( 
            'media_type', media.media_type 
            'media_url', media.media_url 
        ) 
    ) 
) 
FROM deals 
INNER JOIN access ON access.deals_id = deals.id AND access.user_id=$1
INNER JOIN media ON media.deals_id = deals.id 

解决方向与方法 #

方向1：利用Sequelize关联关系（推荐） #

既然使用Sequelize，无需手写复杂SQL，直接通过模型关联实现：

1. 先定义模型间的关联关系：
   • Deal与DealMedia为一对多：Deal.hasMany(DealMedia, { foreignKey: 'deal_id' })
   • 通过access_to_deals筛选客户可访问的Deal
2. 查询时用include嵌套媒体数据：

sync (req, res) => {
    try {
        const customerId = req.query.customer_id;
        const deals = await db.Deal.findAll({
            include: [{
                model: db.DealMedia,
                attributes: ['type', 'media_url'] // 只返回需要的媒体字段
            }],
            where: {
                id: {
                    [db.Sequelize.Op.in]: db.Sequelize.literal(`
                        SELECT deal_id FROM access_to_deals WHERE customer_id = ${customerId}
                    `)
                }
            }
        });
        res.send(deals);
    } catch (err) {
        res.status(500).send(err.message);
    }
};

方向2：修复手写SQL的问题 #

如果坚持手写SQL，针对语法和逻辑错误修正：

1. 修正SQL中的语法错误：
   • json_build_object的键值对之间需加逗号
   • 修正拼写错误：daels.created_at改为deals.created_at，json_build_objecty改为json_build_object
   • 用json_agg替代json_build_array实现媒体数据聚合
2. 修正后的SQL：

SELECT 
    json_build_object(
        'id', deals.id,
        'deal_name', deals.name,
        'pic_url', deals.pic_url,
        'other_data', deals.other_data,
        'media', json_agg(
            json_build_object(
                'media_type', deals_media.type,
                'media_url', deals_media.media_url
            )
        )
    ) AS deal_data
FROM deals
INNER JOIN access_to_deals ON deals.id = access_to_deals.deal_id AND access_to_deals.customer_id = $1
LEFT JOIN deals_media ON deals.id = deals_media.deal_id
GROUP BY deals.id

3. 在Sequelize中执行该查询：

sync (req, res) => {
    try {
        const customerId = req.query.customer_id;
        const [deals] = await db.sequelize.query(
            `SELECT 
                json_build_object(
                    'id', deals.id,
                    'deal_name', deals.name,
                    'pic_url', deals.pic_url,
                    'other_data', deals.other_data,
                    'media', json_agg(
                        json_build_object(
                            'media_type', deals_media.type,
                            'media_url', deals_media.media_url
                        )
                    )
                ) AS deal_data
            FROM deals
            INNER JOIN access_to_deals ON deals.id = access_to_deals.deal_id AND access_to_deals.customer_id = $1
            LEFT JOIN deals_media ON deals.id = deals_media.deal_id
            GROUP BY deals.id`,
            { bind: [customerId], type: db.Sequelize.QueryTypes.SELECT }
        );
        const result = deals.map(item => item.deal_data);
        res.send(result);
    } catch (err) {
        res.status(500).send(err.message);
    }
};

关键注意点 #

• 确保表名与数据库实际表名一致，schema "deals" does not exist错误大概率是表名拼写或schema指定错误
• 聚合查询必须通过GROUP BY包含deals的非聚合字段（PostgreSQL支持按主键分组后查询所有字段）
• Sequelize关联查询更易维护，适合长期项目；手写SQL适合复杂场景，但需注意语法细节

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