Make wordpress join default user metas with a column from another table while displaying the wp_users table on the users screen

Question

I am creating a plugin that adds a custom sortable column(Reputation Score) to the users table displayed at admin page > users, the problem is the column's data should be fetched from a MySQL table wp_user_reputation (userid,user_reputation_score) [created for our own purpose].

My assessment is that I would need the column to be present in query so that I can add sorting capability for it. How do I make make WordPress to join the wp_users table with wp_user_reputation.

I appreciate your help.

Answer 1

To avoid a second query just for sorting you could use the pre_user_query hook to support a custom orderby value.

This code will enable sorting users by reputation in a single query just by setting orderby to reputation.

function wpse_296999_pre_user_query( $user_query ) {
    global $wpdb;

    $order = $user_query->query_vars['order'];
    $orderby = $user_query->query_vars['orderby'];

    // If orderby is 'reputation'.
    if ( 'reputation' === $orderby ) {
        // Join reputation table.
        $user_search->query_from .= " INNER JOIN {$wpdb->prefix}user_reputation AS reputation ON {$wpdb->users}.ID = reputation.userid"; 
        // And order by it.
        $user_search->query_orderby = " ORDER BY reputation.user_reputation_score $order";
    } 
}
add_action('pre_user_query','wpse_27518_pre_user_query');

Just make sure that on this line:

if ( 'reputation' === $orderby ) {

'reputation' is whatever the actual name of your sortable column is.

Answer 2

You would have to construct a custom MySQL-query to retrieve the sorted userid from your wp_user_reputation table. Then you could use the sorted userids to construct a get_users() query with 'include' => $your_retrieved_ids and 'order' => 'include'.