How to sort a non-meta field in the User Admin Panel?

Question

I'm stuck, and haven't been able to find an answer after much searching.

I need to modify the User Admin Panel showing the list of users and the number of posts. I have custom posts (type = 'articles') I, so the default 'posts' only shows zero.

I thought I might be able to change the standard posts count to include my articles, but couldn't find a way. Instead I've added a new column.

I can populate my new column with the proper count of articles. I also have that number linking to the list of articles by that author.

The only thing I cannot figure out is how to sort the column. I've tried every way I could find or think of. I'm just not that deep into WordPress or SQL.

I'm using the following filters.

// add the new column
add_filter( 'manage_users_columns', 'my_users_articles_column' );
function my_users_articles_column( $columns ) {
  $columns['articles_count'] = 'Count';
  return $columns;
}

// get the value for each row
add_filter( 'manage_users_custom_column', 'my_users_articles_column_value', 10, 3 );
function my_users_articles_column_value( $value, $column_name, $id ) {
  if ( $column_name == 'articles_count' ) {
    $count = count_user_posts($id, 'articles');
    $link = '<a href="/wp-admin/edit.php?post_type=articles&author=' . $id . '">' . $count . '</a>';
    $return = ($count > 0) ? $link : $count;
    return $return;
  }
}

// make the column sortable
add_filter( 'manage_users_sortable_columns', 'my_articles_count_column_sortable' );
function my_articles_count_column_sortable( $columns ) {
    $columns['articles_count'] = 'articles_count';
    return $columns;
}

So, after the basic code above, I have the new column for the count of all articles for each author. And it's clickable to trigger a sort, but it's sorting based on the author's name (the first column) not my article count.

I cannot find a way to get this column to sort. Filters/hooks I've tried include 'pre_get_users', 'pre_user_query', 'request', and others. Not sure which is the right filter. But I don't think I have the right sorting method regardless.

The column isn't a meta field. It's populated from a query. I tried something typical, as below, but this doesn't work.

add_filter('pre_get_users', 'my_pre_get_users');
function my_pre_get_users( $vars ) {
    if ( 'article_count' == $vars['orderby'] ) {
        $vars['meta_key'] = 'article_count';
        $vars['orderby'] = 'meta_value_num';
    }
    return $vars;
}

At this point, I could only guess I need to JOIN tables, but that's getting beyond my coding skills. I would continue hacking on this forever at this point.

I've followed answers in several articles such as:

• Sort All Posts by Category Name in the Admin Panel?
• How to make in WordPress admin panel sortable column for the custom field, that count the number of page impressions?
• Sortable Custom Columns in User Panel (users.php)?

Answer 1

The WP_User_Query::parse_orderby() actually supports sorting by the number of posts, but because WP_User_Query::parse_orderby() limits by what users can be sorted, accomplishing a custom sort is a bit of a hack.

Here's the workaround I've created (semi-tested):

add_filter( 'manage_users_sortable_columns', static function ( $columns ) {
    $columns['articles_count'] = array(
        'articles_count',
        false,
        __( 'Articles' ),
        __( 'Table ordered by number of articles.' ),
    );

    return $columns;
} );

/**
 * Because `parse_orderby()` will discard 'articles_count',
 * set it to `post_count`, and create a custom query var for
 * reference later.
 */
add_action( 'pre_get_users', static function ( $query ) {
    if ( 'articles_count' !== $query->get( 'orderby' ) ) {
        return;
    }

    $query->set( 'orderby', 'post_count' );
    $query->set( 'sort_articles_count', true );
} );

/**
 * Check for our custom query var, and change the SQL
 * for sorting users.
 */
add_action( 'pre_user_query', static function ( $query ) {
    if ( ! $query->get( 'sort_articles_count' ) ) {
        return;
    }

    $search  = get_posts_by_author_sql( 'post' );
    $replace = get_posts_by_author_sql( 'articles' );

    $query->query_from = str_replace( $search, $replace, $query->query_from );
} );