The second subquery in SELECT returns more than 1 row; which is invalid in SELECT. I see in you latest edit you've added another column. That's going to give you another different error of:
Error 1241: Operand should contain 1 column(s)
With your current query, you probably can do it like this:
SELECT users.name,
(SELECT COUNT(DISTINCT images.id)
FROM images
WHERE images.user_id = follow.followee
) AS "total_images",
(SELECT GROUP_CONCAT(CONCAT(images.id ,': ', images.path)) AS 'image_and_path'
FROM images
WHERE images.user_id = follow.followee
ORDER BY images.createdAt DESC
LIMIT 3
) AS "recent_images"
FROM follow
LEFT JOIN users ON follow.followee = users.id
WHERE follow.follower = 2;
That will give you results like:
| name |
total_images |
recent_images |
| John |
4 |
1: image_path,2: image_path,3: image_path,4: image_path |
One row with a concatenated columns + a GROUP_CONCAT() from the second subquery in SELECT.
Otherwise, you can do something like this:
SELECT u1.name AS 'Followee',
COUNT(*) OVER (PARTITION BY f.follower) Total_images,
createdAt
FROM users u1
JOIN images i ON u1.id=i.user_id
JOIN follow f ON u1.id=f.followee
WHERE f.follower=2
ORDER BY createdAt DESC
LIMIT 3;
With this results:
| Followee |
Total_images |
id |
path |
createdAt |
| John |
4 |
1 |
image_path |
2021-07-11 05:38:15 |
| John |
4 |
2 |
image_path |
2021-07-10 05:38:15 |
| John |
4 |
3 |
image_path |
2021-07-09 05:38:15 |
Demo fiddle
Update:
To make the value recognizable as JSON array and object, you can add combinations of JSON_OBJECT and JSON_ARRAY. Here's MySQL functions that create JSON values docs:
SELECT JSON_ARRAY(JSON_OBJECT('name',name,'total_images',total_images,'recent_images',
JSON_ARRAY(GROUP_CONCAT(recent_images))))
FROM
(SELECT u.name ,
COUNT(*) OVER (PARTITION BY f.follower) AS total_images,
JSON_OBJECT('id',i.id,'path',i.path) recent_images
FROM follow f
JOIN users u ON f.followee = u.id
JOIN images i ON i.user_id = f.followee
WHERE f.follower = 2
ORDER BY i.createdAt DESC
LIMIT 3) AS V
GROUP BY name, total_images
If LIMIT is not working for you (probably due to sql_mode? I'm not sure), then I'd suggest you use ROW_NUMBER() (or RANK()) function to assign the value with incremental numbering from 1 - XX in descending order. Here's how:
SELECT JSON_ARRAY(JSON_OBJECT('name',name,'total_images',total_images,'recent_images',
JSON_ARRAY(GROUP_CONCAT(recent_images))))
FROM
(SELECT u.name ,
COUNT(*) OVER (PARTITION BY f.follower) AS total_images,
JSON_OBJECT('id',i.id,'path',i.path) recent_images,
ROW_NUMBER() OVER (PARTITION BY f.follower ORDER BY i.createdAt DESC) rn
FROM follow f
JOIN users u ON f.followee = u.id
JOIN images i ON i.user_id = f.followee
WHERE f.follower = 2) AS V
WHERE rn <= 3
GROUP BY name, total_images;
Updated fiddle
