MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE categories
    (`cid` int, `cname` varchar(3), `curl` varchar(4), `csort` int)
INSERT INTO categories
    (`cid`, `cname`, `curl`, `csort`)
VALUES
    (1, 'ccc', 'ccc/', 3),
    (2, 'bbb', 'bbb/', 2),
    (3, 'aaa', 'aaa/', 1),
    (4, 'ddd', 'ddd/', 4)
CREATE TABLE posts
    (`pid` int, `pname` varchar(6), `purl` varchar(15), `cid` int, `padded` datetime, `poffon` int)
INSERT INTO posts
    (`pid`, `pname`, `purl`, `cid`, `padded`, `poffon`)
VALUES
    (1, 'apples', 'apples.html', 2, '2019-01-02 10:11:12', 1),
    (2, 'orange', 'orange.html', 1, '2019-01-02 10:12:00', 1),
    (3, 'grape', 'red-grapes.html', 1, '2019-01-06 10:15:12', 1),
    (4, 'banana', 'bannas.html', 2, '2019-01-08 10:19:54', 0),
    (5, 'kiwi', 'kiwi-fruit.html', 3, '2019-01-10 10:26:20', 1)
CREATE TABLE gallery_photos
    (`gid` int, `pid` int, `gsrc` varchar(10), `gsort` int)
INSERT INTO gallery_photos
    (`gid`, `pid`, `gsrc`, `gsort`)
VALUES
    (1, 1, 'img01a.jpg', 6),
    (2, 1, 'img01b.jpg', 2),
    (3, 1, 'img01c.jpg', 4),
    (4, 3, 'img03a.jpg', 2),
    (5, 3, 'img03b.jpg', 1),
    (6, 3, 'img03c.jpg', 4),
    (7, 3, 'img03d.jpg', 3),
    (8, 2, 'img02a.jpg', 1),
    (9, 1, 'img01d.jpg', 5),
    (10, 1, 'img01e.jpg', 3)
SELECT c.*, pm.pid, pm.pname, pm.purl, pm.padded, pm.poffon, gm.gsrc, gm.gsort
FROM categories c
LEFT JOIN (SELECT p.*
           FROM posts p
           JOIN (SELECT cid, MAX(padded) AS latest
                 FROM posts
                 WHERE poffon = 1
                 GROUP BY cid) pl ON pl.cid = p.cid AND pl.latest = p.padded
          ) pm ON pm.cid = c.cid
LEFT JOIN (SELECT g.*
           FROM gallery_photos g
           JOIN (SELECT pid, MIN(gsort) AS earliest
                 FROM gallery_photos
                 GROUP BY pid) ge ON ge.pid = g.pid AND ge.earliest = g.gsort
          ) gm ON gm.pid = pm.pid
ORDER BY cname


cid
cname
curl
csort
pid
pname
purl
padded
poffon
gsrc
gsort


3
aaa
aaa/
1
5
kiwi
kiwi-fruit.html
2019-01-10 10:26:20
1
null
null

2
bbb
bbb/
2
1
apples
apples.html
2019-01-02 10:11:12
1
img01b.jpg
2

1
ccc
ccc/
3
3
grape
red-grapes.html
2019-01-06 10:15:12
1
img03b.jpg
1

4
ddd
ddd/
4
null
null
null
null
null
null
null

cid	cname	curl	csort	pid	pname	purl	padded	poffon	gsrc	gsort
3	aaa	aaa/	1	5	kiwi	kiwi-fruit.html	2019-01-10 10:26:20	1	null	null
2	bbb	bbb/	2	1	apples	apples.html	2019-01-02 10:11:12	1	img01b.jpg	2
1	ccc	ccc/	3	3	grape	red-grapes.html	2019-01-06 10:15:12	1	img03b.jpg	1
4	ddd	ddd/	4	null	null	null	null	null	null	null