MySQL 8.0

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

select version();


version()


8.0.27


CREATE TABLE albums (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  parent_id INTEGER,
  _lft INTEGER NOT NULL,
  _rgt INTEGER NOT NULL
);

INSERT INTO albums (_lft, _rgt) VALUES
  ( 1,22 )
, ( 2,9 )
, ( 10,21 )
, ( 3,8 )
, ( 4,5 )
, ( 6,7 )
, ( 11,16 )
, ( 17,18 )
, ( 19,20 )
, ( 12,13 )
, ( 14,15 )
;


CREATE TABLE photos (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  album_id INTEGER,
  mime_type VARCHAR (30) NOT NULL,
  width INTEGER NOT NULL,
  height INTEGER NOT NULL,
  is_starred BOOLEAN NOT NULL DEFAULT false
);


INSERT INTO photos (created_at, album_id, mime_type, width, height, is_starred ) VALUES
   (current_date,  1, 'mime', 1, 1, true )
 , (current_date,  2, 'mime', 1, 1, true )
 , (current_date,  3, 'mime', 1, 1, true )
 , (current_date,  4, 'mime', 1, 1, true )
 , (current_date,  5, 'mime', 1, 1, true )
 , (current_date,  6, 'mime', 1, 1, true )
 , (current_date,  7, 'mime', 1, 1, true )
 , (current_date,  8, 'mime', 1, 1, true )
 , (current_date,  9, 'mime', 1, 1, true )
 , (current_date, 10, 'mime', 1, 1, true )
 , (current_date, 11, 'mime', 1, 1, false)
 , (current_date, 12, 'mime', 1, 1, true )
 , (current_date, 13, 'mime', 1, 1, true )
 , (current_date, 14, 'mime', 1, 1, true )
 , (current_date, 15, 'mime', 1, 1, true )
 , (current_date, 16, 'mime', 1, 1, true )
 , (current_date, 17, 'mime', 1, 1, true )
 , (current_date, 18, 'mime', 1, 1, true )
 , (current_date, 19, 'mime', 1, 1, true )
 , (current_date, 20, 'mime', 1, 1, true )
;
UPDATE photos SET created_at = current_date + INTERVAL id hour;
WITH cte AS (
        SELECT covered_albums.*
             , p.id AS photo_id
             , p.album_id AS photo_album
             , ROW_NUMBER() OVER (PARTITION BY covered_albums.id ORDER BY is_starred DESC, created_at DESC) AS rn
          FROM photos AS p
          JOIN albums AS direct_parents ON (direct_parents.id = p.album_id)
          JOIN albums AS covered_albums
            ON direct_parents._lft >= covered_albums._lft
           AND direct_parents._rgt <= covered_albums._rgt
     )
SELECT *
  FROM cte
 WHERE rn = 1
;


id
parent_id
_lft
_rgt
photo_id
photo_album
rn


1
null
1
22
10
10
1

2
null
2
9
6
6
1

3
null
10
21
10
10
1

4
null
3
8
6
6
1

5
null
4
5
5
5
1

6
null
6
7
6
6
1

7
null
11
16
10
10
1

8
null
17
18
8
8
1

9
null
19
20
9
9
1

10
null
12
13
10
10
1

11
null
14
15
11
11
1


SELECT
  best_child_photo.album_id AS album_id,
  best_child_photo.cover_id AS cover_id,
  photos.mime_type AS cover_mime_type,
  photos.width AS cover_width,
  photos.height AS cover_height
FROM (
  SELECT
    covered_albums.id AS album_id,
    (
      SELECT p.id
      FROM photos AS p
      LEFT JOIN albums AS direct_parents ON (direct_parents.id = p.album_id)
      WHERE direct_parents._lft >= covered_albums._lft AND direct_parents._rgt <= covered_albums._rgt
      ORDER BY p.is_starred DESC, p.created_at DESC
      LIMIT 1
    ) AS cover_id
  FROM albums AS covered_albums
) AS best_child_photo
LEFT JOIN photos ON (photos.id = best_child_photo.cover_id);


album_id
cover_id
cover_mime_type
cover_width
cover_height


1
10
mime
1
1

2
6
mime
1
1

3
10
mime
1
1

4
6
mime
1
1

5
5
mime
1
1

6
6
mime
1
1

7
10
mime
1
1

8
8
mime
1
1

9
9
mime
1
1

10
10
mime
1
1

11
11
mime
1
1

id	parent_id	_lft	_rgt	photo_id	photo_album	rn
1	null	1	22	10	10	1
2	null	2	9	6	6	1
3	null	10	21	10	10	1
4	null	3	8	6	6	1
5	null	4	5	5	5	1
6	null	6	7	6	6	1
7	null	11	16	10	10	1
8	null	17	18	8	8	1
9	null	19	20	9	9	1
10	null	12	13	10	10	1
11	null	14	15	11	11	1

album_id	cover_id	cover_mime_type	cover_width	cover_height
1	10	mime	1	1
2	6	mime	1	1
3	10	mime	1	1
4	6	mime	1	1
5	5	mime	1	1
6	6	mime	1	1
7	10	mime	1	1
8	8	mime	1	1
9	9	mime	1	1
10	10	mime	1	1
11	11	mime	1	1