修改用户表搜索结果

时间：2017-12-29 作者：frank astin

您好，我正在使用以下内容修改在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\'
    ),

）；

这将在标准表加载时返回预期结果，但如果不与搜索结合使用，则返回空集。有人能解释一下为什么会这样吗？

2 个回复

SO网友:frank astin

好的，我是这样做的：

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\']."%\') ";
}

}

SO网友:kierzniak

以这种方式构建的查询将按用户的标准属性搜索用户AND 通过此元字段。您可能想使用OR 而不是AND.

要调试查询结果，请尝试找出它生成的SQL。

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\');

我找不到任何允许您根据需要修改查询的钩子，所以我创建了一个简单的类，它将直接替换SQL。

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();

结束

标签： wp-user-query 小码农

小码农CODE

修改用户表搜索结果

相关推荐