WordPress SQL Statement? - DELETE all users WHERE User Role is Subscriber AND have posted NO comment-CodePudding

I have a database with 30K mostly spam user registrations. The site was previously active with users, but has grown quiet. I'd like to clean up the database to remove all of the subscribers in the databse who have not posted any comments. I will leave all other roles (Author, Contributor...) in place.

I've used the following to remove all subscribers in the past...

DELETE 
    wp_users, 
    wp_usermeta 
FROM 
    wp_users 
    INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id 
WHERE 
    meta_key = 'wp_capabilities' AND 
    meta_value LIKE '%subscriber%'

Can the above be adjusted to remove only subscribers with NO comments?

I've also tried this...

DELETE FROM `wp_users`
WHERE ID NOT IN
(SELECT DISTINCT post_author FROM `wp_posts`);

That deleted ALL of the subscribers, AND left all of their comments in place. So that's not right. (It looks to have deleted all users who had no posts.)

Any ideas?

CodePudding user response：

you can try this code,

<?php
add_action('init', 'removeusers');
function removeusers(){
$args = array(
    'role'    => 'subscriber',
    'orderby' => 'user_nicename',
    'order'   => 'ASC'
);

$users = get_users( $args ); 
   foreach ( $users as $user ) {

   $args = array('user_id' => $user->ID);
   $usercomment = get_comments($args);

   if(count($usercomment) < 1){
       require_once(ABSPATH.'wp-admin/includes/user.php' );
       $current_user = wp_get_current_user();
      wp_delete_user( $user->ID );
   } 
   }
}
?>