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.
Comments
Post a Comment