2022-07-31

JOIN 2 tables based on req.query in a 3rd table

I have 3 tables (deals, deals_media, access_to_deals)

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

I'm sending an api get request with a customer_id query parameter.

I want to send back an array of objects with all the deals data the customer is eligible for and to join an array of matching deals_media to the deals.id

I'm using sequalize and await functions and my query looks like:

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) {...}
};

my response data looks like:

   {
       "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
    }
]

and I'd prefer it came out as:

    {
       "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......."
           }
]

I've tried to change the last join to 'LEFT JOIN media ON deals.id = media.deals_id GROUP BY media.deals_id \n' however I get an error to include deals.id and media.id to the GROUP BY and then it produces the same incorrect query result as above. I'm relatively new to SQL so any direction is greatly appreciated. Many thanks for considering my request.



No comments:

Post a Comment