Sortable Custom Columns in User Panel (users.php)?

Question

I'm using Register Plus Redux plugin to augment a registration form with custom metadata fields. These fields display at the bottom of each user record detail page and they can be retrieved with get_the_author_meta.

In addition, I'm able to create columns in the User panel (the list view) and to make these columns sortable. The problem is that, when I click on the custom column heading, the orderby= value in the URL appears to be ignored. Put another way, it appears the query that generates the list view of users does not include the custom meta data (e.g. perhaps it requires a join statement if the metadata is not in the usual place for user data?). It feels like I've missed a step.

Here is my code to create the custom columns:

//add columns to User panel list page
function add_user_columns( $defaults ) {
     $defaults['company'] = __('Company', 'user-column');
     $defaults['title'] = __('Title', 'user-column');
     return $defaults;
}
function add_custom_user_columns($value, $column_name, $id) {
      if( $column_name == 'company' ) {
        return get_the_author_meta( 'company_name', $id );
      }
      if( $column_name == 'title' ) {
        return get_the_author_meta( 'titlefunction', $id );
      }
}
add_action('manage_users_custom_column', 'add_custom_user_columns', 15, 3);
add_filter('manage_users_columns', 'add_user_columns', 15, 1);`

And here is my code to make these columns sortable:

function user_sortable_columns( $columns ) {
    $columns['company'] = 'Company';
    return $columns;
}
add_filter( 'manage_users_sortable_columns', 'user_sortable_columns' );

function user_column_orderby( $vars ) {
    if ( isset( $vars['orderby'] ) && 'company' == $vars['orderby'] ) {
        $vars = array_merge( $vars, array(
            'meta_key' => 'company',
            'orderby' => 'meta_value',
            'order'     => 'asc'
        ) );
    }
    return $vars;
}
add_filter( 'request', 'user_column_orderby' );`

Any ideas how I can update the query that generates the list of users so that it includes sorting by my custom field(s)? Or, if that's not the problem, how to make the custom column headings sort the list of users when clicked?

Thank you.

Answer 1

My first answer was completely off-mark and I'm completely rewriting it...

But thanks to Milo's hint, I found the solution here:
http://wordpress.mcdspot.com/2011/05/24/search-admin-user-list-on-first-and-last-names/

After adapting it, this works for me with 'facebook' and 'twitter' author_meta.

add_action('pre_user_query','wpse_27518_pre_user_query');
function wpse_27518_pre_user_query($user_search) {
    global $wpdb,$current_screen;

    if ( 'users' != $current_screen->id ) 
        return;

    $vars = $user_search->query_vars;

    if('facebook' == $vars['orderby']) 
    {
        $user_search->query_from .= " INNER JOIN {$wpdb->usermeta} m1 ON {$wpdb->users}.ID=m1.user_id AND (m1.meta_key='facebook')"; 
        $user_search->query_orderby = ' ORDER BY UPPER(m1.meta_value) '. $vars['order'];
    } 

    elseif ('twitter' == $vars['orderby']) 
    {
        $user_search->query_from .= " INNER JOIN {$wpdb->usermeta} m1 ON {$wpdb->users}.ID=m1.user_id AND (m1.meta_key='twitter')"; 
        $user_search->query_orderby = ' ORDER BY UPPER(m1.meta_value) '. $vars['order'];
   }
}

Answer 2

I'm not sure it's possible, maybe by modifying the query via pre_user_query. here's a quick and dirty function to view its contents:

add_action('pre_user_query', 'my_user_query');
function my_user_query($userquery){
    print_r($userquery);
    die();
}

Answer 3

Simplest way to do it in my opinion:

<?php

/**
 * Sorting logic for custom columns on WP user table
 */
add_action('pre_user_query', function(WP_User_Query $query) {
  global $wpdb, $current_screen;

  // Bail if viewing anything other than user list in WP Admin
  if ( 'users' != $current_screen->id ) {
    return;
  }

  if ( 'company' === $query->query_vars['orderby'] ) {
    $query->orderby = 'meta_value';
    $query->meta_key = 'company_name';
  }

  else if ( 'title' === $query->query_vars['orderby'] ) {
    $query->orderby = 'meta_value';
    $query->meta_key = 'titlefunction';
  }

}, 10, 1);

Answer 4

If your meta values are not fully populated (has nulls), @brasofilo's excellent answer will produce a user list that omits users with missing meta_values values.

The fix is easy however. Just do a LEFT JOIN:

add_action('pre_user_query','wpse_27518_pre_user_query');
function wpse_27518_pre_user_query($user_search) {
    global $wpdb,$current_screen;

    if ( 'users' != $current_screen->id ) 
        return;

    $vars = $user_search->query_vars;

    if('facebook' == $vars['orderby']) 
    {
        $user_search->query_from .= " LEFT JOIN {$wpdb->usermeta} m1 ON {$wpdb->users}.ID=m1.user_id AND (m1.meta_key='facebook')"; 
        $user_search->query_orderby = ' ORDER BY UPPER(m1.meta_value) '. $vars['order'];
    } 

    elseif ('twitter' == $vars['orderby']) 
    {
        $user_search->query_from .= " LEFT JOIN {$wpdb->usermeta} m1 ON {$wpdb->users}.ID=m1.user_id AND (m1.meta_key='twitter')"; 
        $user_search->query_orderby = ' ORDER BY UPPER(m1.meta_value) '. $vars['order'];
   }
}

$user_search->query_from .= " LEFT JOIN {$wpdb->usermeta} m1 ON {$wpdb->users}.ID=m1.user_id AND (m1.meta_key='facebook')";

Answer 5

I was able to achieve user sorting of a custom column using the code and hooks you've posted, except I did not use the 'request' filter, nor did I need an extra 'orderby' function. Wordpress properly sorts the data in the custom column in my case by the numerical value that I return in the 'manage_users_custom_column' action callback.