How to Remove Two (Related) wp_postmeta Rows?

Question

I have entries in wp_postmeta like so (shortened for brevity:

post_id | meta_key | meta_value

integer | _thumbnail_id | integer

integer | rafi | integer

Now it's easy to select and delete all rows containing _thumb.

Same with rows containing rafi.

What I need to do is delete the rows with a common post_id AND both _thumb and rafi.

The reason is I only want to remove the featured image from posts that have the rafi key set.

I can delete matching _thumbnail_id rows thusly:

global $wpdb;

$wpdb->query( "

DELETE FROM $wpdb->postmeta

WHERE meta_key = '_thumbnail_id'

" );

But I am not sure how to proceed in terms of doing the proper selecting of the matching rows to set up the deletion.

Thanks for any ideas!

Answer 1

After 5 hours of searching finally I wrote the SQL query and that is-

DELETE FROM {$wpdb->postmeta} 
WHERE  post_id IN (SELECT post_id 
                   FROM   (SELECT post_id 
                           FROM   {$wpdb->postmeta} 
                           WHERE  meta_key LIKE '_thumbnail_id' 
                                  AND post_id IN(SELECT post_id 
                                                 WHERE  meta_key LIKE 'rafi')) 
                          AS s) 

I saw your answer above, but using this kinda SQL query for this kinda problem is best practice as well as better.

Hope this helps.

Answer 2

Here's what I came up with. I tested by enabling the "Random Auto Featured Image" plugin, which adds the missing thumbnails and adds the meta key rafi.

I then disable the plugin, and add the following code to functions.php.

Checking the database shows the expected results.

The next step is to incorporate this code into an undo/cleanup function for the plugin.

$sql = "SELECT * from wp_postmeta";

$result = $wpdb->get_results($sql);

foreach( $result as  $results ) {

  $mid = $results->meta_id;
  $pid = (string)$results->post_id;


  if ($results->meta_key == 'rafi_RandFeat'){
    foreach( $result as  $results1 ) {
            if($results1->meta_key == '_thumbnail_id' && $results1->post_id == $pid){
                $wpdb->query( "
                DELETE FROM $wpdb->postmeta
                WHERE meta_id = $mid;
                ");

                $wpdb->query( "
                DELETE FROM $wpdb->postmeta
                WHERE meta_id = $results1->meta_id;
                ");

        };
    };
  };
};