8

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.

1
8

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!

2
  • I don't get it. Reassing posts shouldn't happen i don't want to delete users that posted only those with 0 posts, no reassing should happen. – Michael Rogers Apr 1 '17 at 13:19
  • 1
    It shouldn't affect this specific example as we target any post type and status. This was just to avoid the notice of the wp delete user command.. You can also skip it and press Y or use --yes for all. If you modify the sql to only delete users that published posts, then you might need it to avoid deleting e.g. drafts. Note that you mentioned in the question users that have never published a post.. That strictly means they might have drafts. I assumed you didn't had that in mind ;-) Please be very specific in the question what you want to delete to avoid the guesswork. @MichaelRogers – birgire Apr 1 '17 at 13:38
4

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 );
    }
  }
}
1

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')
2
  • Sorry! I meant custom post types. The author wrote on the support forums it doesn't count custom post types only "post" so it would delete users who made custom posts and that isn't good. – Michael Rogers Apr 1 '17 at 13:13
  • @MichaelRogers I don't think that he means that. As I said the only types that are not counted are the two I wrote. So the plugin should affect custom post types. They are stored in the same database table. I suggest that you do a backup and then try the plugin and see if it works how you want. If it does not, restore the backup. – Nikolay Apr 1 '17 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.