MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.36


CREATE TABLE users 
(id INT,
 name VARCHAR(50));
INSERT INTO users VALUES
(1,'John'),
(2,'Arthur');

CREATE TABLE images 
(id INT,
 user_id INT,
 path VARCHAR(255),
 createdAt DATETIME);

INSERT INTO images VALUES
(1,1,'image_path','2021-07-11 05:38:15'),
(2,1,'image_path','2021-07-10 05:38:15'),
(3,1,'image_path','2021-07-9 05:38:15'),
(4,1,'image_path','2021-07-8 05:38:15');

CREATE TABLE follow 
(id INT,
 followee INT,
 follower INT);

INSERT INTO follow VALUES
(1,1,2);

Records: 2  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0


SELECT u1.name AS 'Followee',
       COUNT(*) OVER (PARTITION BY f.follower) Total_images,
       i.id,
       i.path,
       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


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


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


name
total_images
recent_images


John
4
1: image_path,2: image_path,3: image_path,4: image_path


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


JSON_ARRAY(JSON_OBJECT('name',name,'total_images',total_images,'recent_images',
           JSON_ARRAY(GROUP_CONCAT(recent_images))))


[{"name": "John", "total_images": 4, "recent_images": ["{"id": 1, "path": "image_path"},{"id": 2, "path": "image_path"},{"id": 3, "path": "image_path"}"]}]


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


JSON_ARRAY(JSON_OBJECT('name',name,'total_images',total_images,'recent_images',
           JSON_ARRAY(GROUP_CONCAT(recent_images))))


[{"name": "John", "total_images": 4, "recent_images": ["{"id": 1, "path": "image_path"},{"id": 2, "path": "image_path"},{"id": 3, "path": "image_path"}"]}]


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


id
followee
follower
id
name
id
user_id
path
createdAt
name
total_images
recent_images
rn


1
1
2
1
John
1
1
image_path
2021-07-11 05:38:15
John
4
7b226964223a20312c202270617468223a2022696d6167655f70617468227d
1

1
1
2
1
John
2
1
image_path
2021-07-10 05:38:15
John
4
7b226964223a20322c202270617468223a2022696d6167655f70617468227d
2

1
1
2
1
John
3
1
image_path
2021-07-09 05:38:15
John
4
7b226964223a20332c202270617468223a2022696d6167655f70617468227d
3

1
1
2
1
John
4
1
image_path
2021-07-08 05:38:15
John
4
7b226964223a20342c202270617468223a2022696d6167655f70617468227d
4


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


id
followee
follower
id
name
id
user_id
path
createdAt
name
total_images
recent_images


1
1
2
1
John
1
1
image_path
2021-07-11 05:38:15
John
4
7b226964223a20312c202270617468223a2022696d6167655f70617468227d

1
1
2
1
John
2
1
image_path
2021-07-10 05:38:15
John
4
7b226964223a20322c202270617468223a2022696d6167655f70617468227d

1
1
2
1
John
3
1
image_path
2021-07-09 05:38:15
John
4
7b226964223a20332c202270617468223a2022696d6167655f70617468227d
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