By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE posts (id int(11) primary key, title varchar(255), datetime datetime);
INSERT INTO posts VALUES
(1, 'Post #1', '2021-09-01 09:00:00'),
(2, 'Post #2', '2021-09-01 10:00:00'),
(3, 'Post #3', '2021-09-01 11:00:00'),
(4, 'Post #4', '2021-09-02 09:00:00'),
(5, 'Post #5', '2021-09-02 10:00:00'),
(6, 'Post #6', '2021-09-02 11:00:00'),
(7, 'Post #7', '2021-09-03 09:00:00'),
(8, 'Post #8', '2021-09-03 10:00:00'),
(9, 'Post #9', '2021-09-03 11:00:00'),
(10, 'Post #10', '2021-09-04 09:00:00'),
(11, 'Post #11', '2021-09-04 10:00:00');
CREATE TABLE tags (id int(11), tag varchar(255), slug varchar(255));
INSERT INTO tags VALUES
(1, 'PHP', 'language-php'),
(2, 'JavaScript', 'language-javascript'),
(3, 'Linux', 'server-linux'),
(4, 'Windows', 'server-windows'),
(5, 'Python', 'language-python');
CREATE TABLE tagsmap (id int(11), tag int(11));
INSERT INTO tagsmap VALUES
(1, 1), (1, 2), (2, 2), (2, 3), (3, 1), (3, 2), (3, 3),
(4, 2), (5, 1), (5, 3), (6, 1), (8, 1), (9, 5);
SELECT p.title,
GROUP_CONCAT(t.slug ORDER BY t.id) slugs,
GROUP_CONCAT(t.tag ORDER BY t.id) tags
FROM (SELECT * FROM posts ORDER BY datetime DESC LIMIT 10) p
LEFT JOIN tagsmap tm ON p.id = tm.id
LEFT JOIN tags t ON tm.tag = t.id
WHERE p.datetime <= NOW()
GROUP BY p.id
ORDER BY MAX(p.datetime) DESC;
| title | slugs | tags |
|---|---|---|
| Post #11 | null | null |
| Post #10 | null | null |
| Post #9 | language-python | Python |
| Post #8 | language-php | PHP |
| Post #7 | null | null |
| Post #6 | language-php | PHP |
| Post #5 | language-php,server-linux | PHP,Linux |
| Post #4 | language-javascript | JavaScript |
| Post #3 | language-php,language-javascript,server-linux | PHP,JavaScript,Linux |
| Post #2 | language-javascript,server-linux | JavaScript,Linux |