MySQL 8.0

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

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

cid	cname	curl	gsrc	gsort
3	aaa	aaa/	null	null
2	bbb	bbb/	img01b.jpg	2
1	ccc	ccc/	img03b.jpg	1
4	ddd	ddd/	null	null