fix: improve speed, properly select first item within group window;

This change uses the ROW_RUMBER() window function to select the correct alias within a group. This fixes edge cases where a page in a deeper level is created earlier than its parent page, thus having a lower ID. In this case, intermediate levels did not properly resolve to the correct URL.

The query boils down to the following:

SELECT 
    id, 
    alias_group
FROM (
    SELECT 
        pa.id,
        SUBSTRING_INDEX(pa.alias, '/', 3) AS alias_group,
        ROW_NUMBER() OVER (PARTITION BY SUBSTRING_INDEX(pa.alias, '/', 3) ORDER BY pa.id) AS group_row_num
    FROM 
        path_alias pa
    LEFT OUTER JOIN node_field_data n ON pa.path = CONCAT('/node/', n.nid)
    LEFT OUTER JOIN media_field_data m ON pa.path = CONCAT('/media/', m.mid, '/edit')
    LEFT OUTER JOIN taxonomy_term_field_data t ON pa.path = CONCAT('/taxonomy/term/', t.tid)
    WHERE 
        pa.alias LIKE '/foobar%'
) FilteredAliases
WHERE 
    group_row_num = 1
ORDER BY 
    alias_group ASC
LIMIT 100 OFFSET 0;