How to best delete orphan wp_postmeta

Question

I'm in the middle of doing some fairly complex queries involving metadata from a custom post type. Unfortunately if any posts are deleted the metadata stays put which complicates matters as I am counting it.

I was wondering how best to go about deleting the orphan metadata in the event that the post is deleted.

Should I include a function that deletes them the database through $wpdb?

I found an SQL query that does just that but I'm not sure how to format it.

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

I tried it in phpmyadmin on a backup copy of my database but to no avail.

Answer 1

This snippet is good enough, use it with $wpdb. In this case I would use :

<?php
add_action( 'before_delete_post', 'my_func' );
function my_func( $postid ){

// We check if the global post type isn't ours and just return
global $post_type;   
if ( $post_type != 'my_custom_post_type' ) return;

// My custom stuff for deleting my custom post type here
}
?>

Source