I'm trying to create a query that returns all the images of a user in a categorized fashion, so the response looks like this:
{
totalAvatarImgs: 6,
totalCoverImgs: 2,
totalPostImgs: 0,
avatarImgs: [{
id: 1,
img: "avatar-1.jpg"
},{
id: 2,
img: "avatar-2.jpg"
},{
id: 3,
img: "avatar-3.jpg"
}],
coverImgs: [{
id: 4,
img: "cover-1.jpg"
},{
id: 5,
img: "cover-2.jpg"
}],
postImgs: []
}
This is my images
& image_categories
table structures:
The query looks like this, but it throws Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'db.images.id'; this is incompatible with sql_mode=only_full_group_by
SELECT
a.total AS totalAvatarImgs,
c.total AS totalCoverImgs,
p.total AS totalPostImgs,
CONCAT('[', GROUP_CONCAT(DISTINCT (JSON_OBJECT('id', a.id, 'img', a.img))), ']') AS avatarImgs,
CONCAT('[', GROUP_CONCAT(DISTINCT (JSON_OBJECT('id', c.id, 'img', c.img))), ']') AS coverImgs,
CONCAT('[', GROUP_CONCAT(DISTINCT (JSON_OBJECT('id', p.id, 'img', p.img))), ']') AS postImgs
FROM image_categories AS ic
LEFT JOIN images AS i ON i.imgCatID = ic.id
JOIN (SELECT id, img, COUNT(*) AS total FROM images WHERE imgCatID = 2 AND uID = 10 LIMIT 3) AS a
JOIN (SELECT id, img, COUNT(*) AS total FROM images WHERE imgCatID = 3 AND uID = 10 LIMIT 3) AS c
JOIN (SELECT id, img, COUNT(*) AS total FROM images WHERE imgCatID = 1 AND uID = 10 LIMIT 3) AS p
WHERE i.uID = 10;
How should I refactor this query to make it work in strict mode?
Thank you in advance!