Make Members table list first name and last name columns sortable

members_table_list_sort_by_first_and_last_name.php

<?php
/**
 * Make Members table list first name and last name columns sortable
 * 
 * Add this code to your site by following this guide - https://www.paidmembershipspro.com/create-a-plugin-for-pmpro-customizations/
 */

function my_pmpro_members_list_sql( $sqlQuery ) {
    global $wpdb;
    // Bail if the query is not for ordering by last name or first name. Check request
    if ( ! isset( $_REQUEST['orderby'] ) || ( $_REQUEST['orderby'] !== 'last_name'  && $_REQUEST['orderby']  !== 'first_name' ) ) {
		  return $sqlQuery;
	  }
	
	// some vars for the search
	if ( isset( $_REQUEST['l'] ) ) {
		$l = sanitize_text_field( $_REQUEST['l'] );
	} else {
		$l = false;
	}

	$search_key = false;
	if( isset( $_REQUEST['s'] ) ) {
		$s = trim( sanitize_text_field( $_REQUEST['s'] ) );
	} else {
		$s = '';
	}

	// If there's a colon in the search, let's split it out.
	if( ! empty( $s ) && strpos( $s, ':' ) !== false ) {
		$parts = explode( ':', $s );
		$search_key = array_shift( $parts );
		$s = implode( ':', $parts );
	}

	// Treat * as wild cards.
	$s = str_replace( '*', '%', $s );

	// some vars for ordering
	if(isset($_REQUEST['orderby'])) {
		$orderby =  sanitize_text_field( $_REQUEST['orderby'] );
		if( $_REQUEST['order'] == 'asc' ) {
			$order = 'ASC';
		} else {
			$order = 'DESC';
		}
	} else {
		if ( 'oldmembers' === $l || 'expired' === $l || 'cancelled' === $l ) {
			$orderby = 'enddate';
			$order = 'DESC';
		} else {
			$orderby = 'u.user_registered';
			$order = 'DESC';
		}
	}

	// some vars for pagination
	if(isset($_REQUEST['paged'])) {
		$pn = intval($_REQUEST['paged']);
	} else {
		$pn = 1;
	}

	//hardcoded to 20, change as needed
	$limit = 20;
	$end = $pn * $limit;
	$start = $end - $limit;


	$sqlQuery =  "SELECT u.ID, u.user_login, u.user_email, u.display_name, mu.membership_id, m.name AS membership, MAX(CASE WHEN um_last.meta_key = 'last_name' THEN um_last.meta_value END) AS last_name, MAX(CASE WHEN um_last.meta_key = 'first_name' THEN um_last.meta_value END) AS first_name,
		UNIX_TIMESTAMP(u.user_registered) AS joindate, 
      	UNIX_TIMESTAMP(mu.startdate) AS startdate, 
        UNIX_TIMESTAMP(mu.enddate) AS enddate 
		FROM $wpdb->users u 
		LEFT JOIN  $wpdb->pmpro_memberships_users mu ON u.ID = mu.user_id "; 

			if ( 'oldmembers' === $l || 'expired' === $l || 'cancelled' === $l ) {
				$sqlQuery .= " LEFT JOIN $wpdb->pmpro_memberships_users mu2 ON u.ID = mu2.user_id AND mu2.status = 'active' ";
			}

			$sqlQuery .= "LEFT JOIN $wpdb->pmpro_membership_levels m ON mu.membership_id = m.id 
			LEFT JOIN $wpdb->pmpro_subscriptions s ON mu.user_id = s.user_id 
			LEFT JOIN $wpdb->usermeta um_last ON u.ID = um_last.user_id 
			WHERE 	mu.membership_id > 0 
			AND mu.status = 'active' 
			AND um_last.meta_key IN ('first_name', 'last_name')";


			if ( !empty( $s ) ) {
				if ( ! empty( $search_key ) ) {
					// If there's a colon in the search string, make the search smarter.
					if( in_array( $search_key, array( 'login', 'nicename', 'email', 'url', 'display_name' ), true ) ) {
						$key_column = 'u.user_' . $search_key; // All search key options above are safe for use in a query.
						$search_query = " AND $key_column LIKE '%" . esc_sql( $s ) . "%' ";
					} elseif ( $search_key === 'discount' || $search_key === 'discount_code' || $search_key === 'dc' ) {
						$user_ids = $wpdb->get_col( "SELECT dcu.user_id FROM $wpdb->pmpro_discount_codes_uses dcu LEFT JOIN $wpdb->pmpro_discount_codes dc ON dcu.code_id = dc.id WHERE dc.code = '" . esc_sql( $s ) . "'" );
						if ( empty( $user_ids ) ) {
							$user_ids = array(0);	// Avoid warning, but ensure 0 results.
						}
						$search_query = " AND u.ID IN(" . implode( ",", $user_ids ) . ") ";					
					} elseif ( $search_key === 'subscription_transaction_id' ) {
						$search_query = " AND s.subscription_transaction_id LIKE '%" . esc_sql( $s ) . "%' AND mu.membership_id = s.membership_level_id AND mu.status = 'active' ";
					} else {
						$user_ids = $wpdb->get_col( "SELECT user_id FROM $wpdb->usermeta WHERE meta_key = '" . esc_sql( $search_key ) . "' AND meta_value LIKE '%" . esc_sql( $s ) . "%'" );
						if ( empty( $user_ids ) ) {
							$user_ids = array(0);	// Avoid warning, but ensure 0 results.
						}
						$search_query = " AND u.ID IN(" . implode( ",", $user_ids ) . ") ";
					}
				} else {
					// Default search checks a few fields.
					$search_query = " AND ( u.user_login LIKE '%" . esc_sql($s) . "%' OR u.user_email LIKE '%" . esc_sql($s) . "%' OR um.meta_value LIKE '%" . esc_sql($s) . "%' OR u.display_name LIKE '%" . esc_sql($s) . "%' OR ( s.subscription_transaction_id LIKE '%" . esc_sql( $s ) . "%' AND mu.membership_id = s.membership_level_id AND s.status = 'active' ) ) ";
				}

				$sqlQuery .= $search_query;
			}
	
			if ( 'oldmembers' === $l ) {
				$sqlQuery .= " AND mu.status <> 'active' AND mu2.status IS NULL ";
			} elseif ( 'expired' === $l ) {
				$sqlQuery .= " AND mu.status = 'expired' AND mu2.status IS NULL ";
			} elseif ( 'cancelled' === $l ) {
				$sqlQuery .= " AND mu.status IN('cancelled', 'admin_cancelled') AND mu2.status IS NULL ";
			} elseif ( $l ) {
				$sqlQuery .= " AND mu.status = 'active' AND mu.membership_id = '" . (int) $l . "' ";
			} else {
				$sqlQuery .= " AND mu.status = 'active' ";
			}

			$sqlQuery .= " GROUP BY u.ID, mu.membership_id HAVING first_name IS NOT NULL AND last_name IS NOT NULL ORDER BY $orderby  $order  LIMIT $start, $end";

    return $sqlQuery;

}
add_filter( "pmpro_members_list_sql", "my_pmpro_members_list_sql" );


function my_pmpro_members_list_table_sortable_columns( $columns ) {
	$columns['first_name'] = array( 'first_name', false);
	$columns['last_name'] = array( 'last_name', false );
	return $columns;
}


add_filter('pmpro_members_list_table_sortable_columns', 'my_pmpro_members_list_table_sortable_columns');