Query TYPO3 page tree with MySQL 8+

query_page_tree.sql

set @rootPageId := 15047;
set @maxDepth := 99;

with recursive children as (
    select root.*, 1 as depth
    from pages root
    where root.uid = @rootPageId
       or (root.sys_language_uid > 0 and root.l10n_source = @rootPageId)
      and root.deleted = 0
    union all
    select parent.*, child.depth + 1 as depth
    from pages parent
    inner join children child
    on (child.sys_language_uid = 0 and parent.pid = child.uid)
    where parent.deleted = 0
      and child.depth < @maxDepth
)
select uid, pid, depth, sys_language_uid, l10n_source
from children
order by depth, sys_language_uid;