How to bulk delete all users with no posts?

Question

I have 10 thousand of users about 98% of these users never published a post (i mean post not comments).

I need a way to mass delete users with 0 posts.

The method must count all posts included custom post types and has to use proper WordPress function to delete a user as if they were manually deleted in the dashboard and not just drop a table/row on mysql, since that might cause unexpected results.

There's an old plugin that does this but doesn't consider all post types so can't really be used.

Any help appreciated.

Answer 1

If you have a large number of users to delete, you might consder using the wp user delete wp-cli command to avoid script timeouts.

Here's an example of a SQL query to delete all users without posts of any type and status.

You can therefore try this untested one-liner:

wp user delete $(wp db query "SELECT ID FROM wp_users WHERE ID NOT IN (SELECT DISTINCT post_author FROM wp_posts ) AND ID NOT IN (1,2,3)" | tail -n +2 ) --reassign=1

or in expanded form:

wp user delete $(wp db query
    "SELECT ID  
         FROM wp_users   
         WHERE ID NOT IN (  
            SELECT DISTINCT post_author FROM wp_posts 
         ) AND ID NOT IN (1,2,3)" | tail -n +2 
  ) --reassign=1

Note that we added an extra AND ID NOT IN (1,2,3) restriction to make sure these users are not deleted (e.g. admin users). You will have to adjust it to your needs and also the table prefix wp_.

When I briefly tested this for couple of users, I noticed I had to add the tail -n +2 part to avoid the top 3 lines in the header and the table border of the wp db query output.

Here we reassign all posts to user 1, to avoid the notice:

--reassign parameter not passed. All associated posts will be deleted. Proceed? [y/n] 

Hope you can adjust it further to your needs, like relaxing the user delete conditions by adding WHERE post_status = 'publish'.

Note: Remember to backup before testing!

Answer 2

Here's a way to do it in PHP. It might be slow and/or timeout, but since it's a one-time thing, it shouldn't matter too much. Temporarily place inside your functions.php or upload as a new plugin.

//* You don't want to run this on admin_init. Run it once and deactivate
add_action( 'admin_init', 'wpse_262100_admin_init' );
function wpse_262100_admin_init() {
  $reserved_post_types = [
    'attachment',
    'revision',
    'nav_menu_item',
    'custom_css',
    'customize_changeset',
  ];

  //* Get the non-reserved post types
  $post_types = array_diff( array_keys( get_post_types() ), $reserved_post_types );
  foreach( get_users() as $user ) {
    if( 0 == count_user_posts( $user->ID, $post_types ) ) {
      wp_delete_user( $user->ID );
    }
  }
}

Answer 3

Judging by the source code of the old plugin you mentioned, the post types that it does not consider are attachment and revision. I think you can easily fix this by removing this from the source code of the plugin file no-posts-user-delete.php

    AND NOT WP.post_type in ('attachment', 'revision')