Hey Shamim,
What are your thoughts on this? This seems to get the job done.
add_filter( 'fep_filter_message_query_sql', 'fep_filter_message_query_sql', 10, 2 );
/**
* Allows for the searching of student number on the message list page
*
* @param $sql
* @param $class
* @return mixed
*/
function fep_filter_message_query_sql( $sql, $class ) {
if( isset( $_GET['fep-search'] ) && ! empty( $_GET['fep-search'] ) &&
preg_match( '/^\d{7}$/', trim( strtolower( $_GET['fep-search'] ) ) )
) {
global $wpdb;
// Inject DISTINCT cause the join seems to bring duplicate records
$sql = str_replace( 'SELECT ', 'SELECT DISTINCT ', $sql );
// Inject the users table join
$sql = str_replace( ' WHERE', ' INNER JOIN ' . $wpdb->users . ' on adm_fep_messages.mgs_author = ' . $wpdb->users . '.ID WHERE', $sql );
// Inject for the where on user_login = ?
$sql = str_replace( ' ORDER BY',' OR ( ' . $wpdb->users . '.user_login = "' . esc_sql( trim( strtolower( $_GET[ 'fep-search' ] ) ) ) . '") ORDER BY', $sql );
}
return $sql;
}