How to get an attachment id from a filename

I worked this out eventually by tracing through the code for get_attached_file() to see where it was getting the filename from, and reverse engineered the following:

function get_attachment_id_by_filename($filename) {
    global $wpdb;
    $sql = $wpdb->prepare("SELECT * FROM  $wpdb->posts WHERE  post_type="attachment" and guid like %s order by post_date desc", "%$filename");
    $attachments = $wpdb->get_results($sql, OBJECT);
    return $attachments[0]->ID ?? false;
}

Note that it is possible the query will return more than one row if the same file basename exists in different media library folders. This routine will however only return the most recent found.

This works for everything I can throw at it so far, but welcome any issues people may be aware of with this solution.