Make a custom column sortable, by custom post count

Question

I have the following code which at the moment displays all posts from post_type product in a column called Art in the users section of wp-admin. (slightly modified from https://pippinsplugins.com/add-user-id-column-to-the-wordpress-users-table/)

add_filter('manage_users_columns', 'pippin_add_user_id_column');
function pippin_add_user_id_column($columns) {
    $columns['art'] = 'Art';
    return $columns;
}

add_action('manage_users_custom_column',  'pippin_show_user_id_column_content', 10, 3);
function pippin_show_user_id_column_content($value, $column_name, $user_id) {
    query_posts(array( 
            'post_type' => 'product',
            'author' => $user_id,
        ) ); 
            $count = 0;
            while (have_posts()) : the_post(); 
                $count++; 
            endwhile;
            // echo $count;
            $art = $count;
        wp_reset_query();
    if ( 'art' == $column_name ) {
        $pwc_link = admin_url() . "edit.php?post_type=product&author=".$user_id;
        return '<a href="'.$pwc_link.'" target="_blank">'.$art.'</a>';
    }
    return $value;
}

I've found this tutorial, which explains how to make admin columns sortable http://code.tutsplus.com/articles/quick-tip-make-your-custom-column-sortable--wp-25095 and so I added the following code afterwards:

add_filter( 'manage_users_sortable_columns', 'my_sortable_cake_column' );
function my_sortable_cake_column( $columns ) {
    $columns['art'] = 'art';

    //To make a column 'un-sortable' remove it from the array
    //unset($columns['date']);

    return $columns;
}

But the part I'm stuck on now, is telling WordPress how to sort the data. The example they use is:

add_action( 'pre_get_posts', 'my_slice_orderby' );
function my_slice_orderby( $query ) {
    if( ! is_admin() )
        return;

    $orderby = $query->get( 'orderby');

    if( 'slice' == $orderby ) {
        $query->set('meta_key','slices');
        $query->set('orderby','meta_value_num');
    }
}

But there is no meta_key or query that I know of where I can orderby a custom post type count... So I'm a bit stuck. I'm sure its a case of doing another query_posts with post count, but how do I order results based from that?

Any help would be great, all I want to do is order the users by Art (custom post type product) count when you click on the Art column.

Answer 1

Users are queried differently than posts so you have to hook into the 'pre_user_query' action to filter user queries. Let me know if this works:

add_action( 'pre_user_query', 'my_pre_user_query', 1 );
function my_pre_user_query( $query ) {
    global $wpdb, $current_screen;

    // Only filter in the admin
    if ( ! is_admin() )
        return;

    // Only filter on the users screen
    if ( ! ( isset( $current_screen ) && 'users' == $current_screen->id ) )
        return;

    // Only filter if orderby is set to 'art'
    if ( isset( $query->query_vars ) && isset( $query->query_vars[ 'orderby' ] )
        && ( 'art' == $query->query_vars[ 'orderby' ] ) ) {

        // We need the order - default is ASC
        $order = isset( $query->query_vars ) && isset( $query->query_vars[ 'order' ] ) && strcasecmp( $query->query_vars[ 'order' ], 'desc' ) == 0 ? 'DESC' : 'ASC';

        // Order the posts by product count
        $query->query_orderby = "ORDER BY ( SELECT COUNT(*) FROM {$wpdb->posts} products WHERE products.post_type = 'product' AND products.post_status = 'publish' AND products.post_author = {$wpdb->users}.ID ) {$order}";

    }

}