1

Hi i am using the following to modify the results returned from a search on the users tables in wp admin :

add_action('pre_get_users','custom_user_search');

 $query->query_vars['meta_query'] = array(
'relation' => 'OR',
    array(
        'key' => 'billing_postcode',
        'value' => str_replace('*', '',  $query->query_vars['search']),
        'compare' => 'LIKE'
    ),

);

This returns the expected results on a standard table load, but when not in conjunction with a search returns an empty set. Can someone explain why this?

  • "Does not work" means the action doesn't get triggered, or you are not receiving the results you expected? Does $query->request show the correct SQL? – janh Dec 29 '17 at 13:13
1

OK, Here's how I did it :

add_action('pre_user_query', 'custom_user_list_queries');

function custom_user_list_queries($query){ 

if(!empty($query->query_vars['search'])) {
    $query->query_from .= "  LEFT OUTER JOIN wp_usermeta AS alias ON (wp_users.ID = alias.user_id)";//note use of alias
    $query->query_where .= " OR ".
          "(alias.meta_key = 'billing_postcode' AND alias.meta_value LIKE '%".$query->query_vars['search']."%') ".
          " OR ".
          "(alias.meta_key = 'first_name' AND alias.meta_value LIKE '%".$query->query_vars['search']."%') ".
          " OR ".
          "(alias.meta_key = 'last_name' AND alias.meta_value LIKE '%".$query->query_vars['search']."%') ".
          " OR ".
          "(alias.meta_key = 'chat_name' AND alias.meta_value LIKE '%".$query->query_vars['search']."%') ";
}

}
0

The query built in this way will search for users by their standard properties AND by this meta field. You want to probably use OR instead of AND.

To debug query results try to find out what SQL it produce.

function wpse_289735_debug_user_query( $query ) {

    echo "SELECT $query->query_fields $query->query_from $query->query_where $query->query_orderby $query->query_limit";
    die();
}

add_filter('pre_user_query', 'wpse_289735_debug_user_query');

I could not find any hook which will allow you to modify query to your needs so I crated simple class which will replace SQL directly.

class WPSE_289735_User_Search {

    /**
     * Search phrase
     *
     * @var string
     */
    private $search;

    /**
     * Class constructor
     */
    public function __construct()
    {
        $this->define_hooks();
    }

    /**
     * Filter used to save search phrase and add filter which will replace SQL
     */
    public function add_custom_search_filter( $query ) {

        if( isset( $query->query_vars['search'] ) && !empty( $query->query_vars['search'] ) ) {

            /**
             * Save search phrase
             */
            $this->search = str_replace('*', '', $query->query_vars['search']);

            /**
             * Add filter only when searching users
             */
            add_filter( 'query', array($this, 'replace_user_search_sql') );
        }
    }

    /**
     * Replace user search SQL with own SQL
     */
    public function replace_user_search_sql( $query ) {

        /**
         * Immediately remove filter to not break other queries
         */
        remove_filter( 'query', array($this, 'replace_user_search_sql') );

        global $wpdb;

        $sql[] = "SELECT DISTINCT SQL_CALC_FOUND_ROWS $wpdb->users.ID";
        $sql[] = "FROM $wpdb->users";
        $sql[] = "INNER JOIN $wpdb->usermeta ON ($wpdb->users.ID = $wpdb->usermeta.user_id)";

        $sql[] = "WHERE 1=1";

        $sql[] = "AND";
        $sql[] = "(";
        $sql[] =     "($wpdb->usermeta.meta_key = 'billing_postcode' AND $wpdb->usermeta.meta_value LIKE '%s')";
        $sql[] =     "OR";
        $sql[] =     "($wpdb->users.user_login LIKE '%s' OR $wpdb->users.user_url LIKE '%s' OR $wpdb->users.user_email LIKE '%s' OR $wpdb->users.user_nicename LIKE '%s' OR $wpdb->users.display_name LIKE '%s')";
        $sql[] = ")";

        $sql[] = "ORDER BY $wpdb->users.user_login ASC LIMIT 0, 20";

        return $wpdb->prepare( join(" ", $sql), $this->search, $this->search, $this->search, $this->search, $this->search, $this->search );
    }

    /**
     * Define plugin related hooks
     */
    private function define_hooks() {

        /**
         * Filter used to save search phrase and add filter which will replace SQL
         */
        add_action('pre_get_users', array( $this, 'add_custom_search_filter'));
    }
}

new WPSE_289735_User_Search();
  • Could i have some usage instructions or will this run out of the box? – frank astin Dec 29 '17 at 18:46
  • This will run out of the box. It is working only for your meta key ‘billing_postcode’. – kierzniak Dec 29 '17 at 18:55
  • No, it is not working, just returns an empty set. – frank astin Dec 29 '17 at 19:27
  • Echo ‘$wpdb->prepare’ output and play with SQL in phpmyadmin. I tested this code, there must be some issue with naming or data structure. – kierzniak Dec 29 '17 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.