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

Question

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.

UPDATE: As to @NBK's suggestion, I've updated the query below however I'm running into the following error Error is Deals: SequelizeDatabaseError: schema "deals" does not exist

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

please use only joins for tables , it is already aroud for 30 years, mixing them togehter is a bad habit — nbk, Jul 24 '22 at 01:33
See [Bad habbits](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) to kick. — Stu, Jul 24 '22 at 08:31
your query should look like https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c5b44be730fcf498d0bc3e13a7d859ec and also please explain what is not working any error message? — nbk, Jul 24 '22 at 10:54
Hey @Stu, Thanks for that! was very clear about some best practices I was unaware of. I've updated the question as your direction has helped in rewriting my query — Kirill, Jul 30 '22 at 20:18
@nbk, thank you for that example. I changed it from LEFT JOIN to JOIN as that gave me only the deals that matched the access the user had. I've updated the question above as my query result is not organized properly. Any thoughts will go a long way Thanks for all your help thus far! — Kirill, Jul 30 '22 at 20:21
Does this answer your question? [Using row\_to\_json() with nested joins](https://stackoverflow.com/questions/13227142/using-row-to-json-with-nested-joins) — nbk, Jul 30 '22 at 21:00
@NBK that definitely helped! It's not recognizing my deals schema (which it still recognizes with the old query). I posted the updated query in the question. Thoughts? — Kirill, Jul 31 '22 at 03:50
Sorry you lost me, I can' see any as chena in your code and I really though about TT be as t you wo zu l es only use JSON for the media column. The rest c as n já alone — nbk, Jul 31 '22 at 06:17

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

0 Answers0