0

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:

enter image description here

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!

Csaba
  • 1,945
  • 3
  • 28
  • 46
  • You use implicit GROUP BY - in this case each row is separate group. So GROUP_CONCAT() usage makes no sense - remove it. – Akina Aug 12 '20 at 13:55
  • If I remove the GROUP_CONCAT, I also have to remove the DISTINCT, then it will return the results three times as the LIMIT is set to 3. – Csaba Aug 12 '20 at 16:53
  • If so add proper GROUP BY. – Akina Aug 12 '20 at 17:25

0 Answers0