Getting an ID from a URL, why can WP do this itself, but none of of the functions provided get it right?

Question

I have a set of absolute URLs that have been used to refer to my site (they come from redirects in an old .htaccess file in this case). All of these URLs do resolve to some page or post on the site. In other words, when I use them in my browser, they work.

Many of these are somehow internally redirected by WordPress to current forms of that post's permalink. So a URL recorded as /my-old-category/my-post-slug may turn out to be at /my-new-category/my-post-slug instead. WordPress has no problem coping with these changes in practice. I don't land on 404's when I use the old URLs.

However, when I programmatically try to discover the ID of the target pages and posts, WordPress does not do so well. In this case, from a list of a few thousand URLs, I end up with a few hundred misses. Neither url_to_postid nor get_page_by_path seems to work consistently. Here is the code that I've had the best luck with, but it still leaves me a few hundred lookups short.

foreach ( $array_of_urls as $url ) {
    $id = url_to_postid( $url );
    if ( $id == 0 ) {
        $base = basename( untrailingslashit( $url ) );
        $post = get_page_by_path( $base , OBJECT, 'page' );
        if ( $post ) {
            $id = $post->ID;
        } else {
            $post = get_page_by_path( $base , OBJECT, 'post' );
            if ( $post ) {
                $id = $post->ID;
            }
        }
    }
    echo "$id\t$url\n";
}

Is there a better way to do this? Like I say, when I actually use these old URLs on the site WordPress is able to find the appropriate page or post. Why can't it provide the ID for those pages and posts to me in PHP?

[By the way, yes, I know there are a number of other posts on StackExchange addressing aspects of this issue. But none seem to get to the bottom of it for me. Sorry to retread old ground.]

Update

Based on the guidance from @Milo below, I rewrote the code with bits of redirect_guess_404_permalink and had a much better lookup rate.

foreach ( $array_of_urls as $url ) {
    $where = $wpdb->prepare("post_name LIKE %s", $wpdb->esc_like( basename( untrailingslashit( $url ) ) ) . '%');
    $id = $wpdb->get_var("SELECT ID FROM $wpdb->posts WHERE $where AND post_status = 'publish'");
    echo "$id\t$url\n";
}

Can anyone improve on this?

Answer 1

It may seem like WordPress is intelligently redirecting old URLs, but what it's doing is just guessing, and mostly getting it right due to the nature of your posts and URL structure being unique enough for it to guess correctly. There are cases where it'll get this guess wrong.

You can see where it does this in WordPress source, the function is aptly named redirect_guess_404_permalink.

API functions don't guess, it's either an exact match or it's not. If you think about this, it makes sense. You could duplicate that functionality and do the same LIKE query on post_name if you want to try to find a possible match the same way WordPress does.