how to search custom post meta keys on the search box above the posts columns

wptutor how to search custom post meta keys on the search box above the posts columns已关闭评论 4 浏览量

version one

/**
 * Extend admin search to include custom meta fields.
 * * @param string   $search Admin search SQL.
 * @param WP_Query $wp_query The current query object.
 * @return string  Modified search SQL.
 */
add_filter('posts_search', 'wp_admin_search_custom_meta', 10, 2);
function wp_admin_search_custom_meta($search, $wp_query) {
    global $wpdb;

    // 1. Only run this in the admin dashboard and on the main query
    if (!is_admin() || !$wpdb || !$wp_query->is_main_query()) {
        return $search;
    }

    // 2. Target specific post types (e.g., 'post', 'page', or a Custom Post Type)
    // Replace 'post' with your specific CPT if needed, or remove this check for all post types.
    if ($wp_query->get('post_type') !== 'post') {
        return $search;
    }

    // 3. Get the search term
    $search_term = $wp_query->get('s');
    if (empty($search_term)) {
        return $search;
    }

    // 4. Define the custom meta keys you want to search
    // EDIT THIS ARRAY with your actual meta keys
    $meta_keys_to_search = array('_my_custom_key_1', 'billing_phone', 'serial_number');

    // Escape the search term for SQL safety
    $escaped_search_term = $wpdb->esc_like($search_term);
    $search_wildcard     = '%' . $escaped_search_term . '%';

    // 5. Build the subquery for meta keys
    $meta_key_placeholders = implode("', '", array_map('esc_sql', $meta_keys_to_search));
    
    $meta_subquery = $wpdb->prepare(
        "OR $wpdb->posts.ID IN (
            SELECT post_id 
            FROM $wpdb->postmeta 
            WHERE meta_key IN ('$meta_key_placeholders') 
            AND meta_value LIKE %s
        )",
        $search_wildcard
    );

    // 6. Inject our subquery right after the post_title/post_content checks
    // WordPress wraps the title/content search in parentheses; we append our 'OR' condition inside it.
    if (!empty($search)) {
        $search = preg_replace('/^\s*AND\s*\(/', 'AND (', $search); // Clean up formatting if necessary
        $search = rtrim($search, ' )') . ' ' . $meta_subquery . ' )';
    }

    return $search;
}

version two, allow to search any meta key, but pay attension to the sql degrade performance

/**
 * Extend admin search to include ANY custom meta field value.
 * * @param string   $search Admin search SQL.
 * @param WP_Query $wp_query The current query object.
 * @return string  Modified search SQL.
 */
add_filter('posts_search', 'wp_admin_search_all_custom_meta', 10, 2);
function wp_admin_search_all_custom_meta($search, $wp_query) {
    global $wpdb;

    // 1. Safety Checks: Only run in admin dashboard and on the main query
    if (!is_admin() || !$wpdb || !$wp_query->is_main_query()) {
        return $search;
    }

    // 2. Restrict to specific post type if needed (highly recommended for performance)
    if ($wp_query->get('post_type') !== 'post') {
        return $search;
    }

    // 3. Extract and validate the search term
    $search_term = $wp_query->get('s');
    if (empty($search_term)) {
        return $search;
    }

    // 4. Escape the search term safely for SQL
    $escaped_search_term = $wpdb->esc_like($search_term);
    $search_wildcard     = '%' . $escaped_search_term . '%';

    /**
     * 5. Build the global subquery.
     * We exclude meta_keys starting with underscores (like '_edit_lock', '_thumbnail_id') 
     * because they are internal, system-level keys and rarely contain user-searchable content.
     * We also avoid matching serialized arrays/objects to prevent database drag.
     */
    $meta_subquery = $wpdb->prepare(
        "OR $wpdb->posts.ID IN (
            SELECT post_id 
            FROM $wpdb->postmeta 
            WHERE meta_key NOT LIKE '\_%'
            AND meta_value LIKE %s
            AND meta_value NOT LIKE 'a:%%:{%%}' 
            AND meta_value NOT LIKE 'O:%%:\"%%\"%%'
        )",
        $search_wildcard
    );

    // 6. Inject the global meta query into the native WordPress search logic
    if (!empty($search)) {
        $search = preg_replace('/^\s*AND\s*\(/', 'AND (', $search);
        $search = rtrim($search, ' )') . ' ' . $meta_subquery . ' )';
    }

    return $search;
}

version three check version 正确版本

/**
 * Meta keys searched by the admin list search box (edit.php).
 * Includes Polylang-suffixed keys (e.g. product_mpn_en) when Polylang is active.
 */
function tqb_admin_search_meta_keys(): array {
    $keys = apply_filters('tqb_admin_search_meta_keys', [
        'product_mpn',
        'oem_num',
        'cross_ref',
    ]);

    if (!function_exists('pll_languages_list')) {
        return $keys;
    }

    $expanded = [];
    foreach ($keys as $key) {
        $expanded[] = $key;
        foreach (pll_languages_list(['fields' => 'slug']) as $lang) {
            $expanded[] = $key . '_' . $lang;
        }
    }

    return array_unique($expanded);
}

/**
 * Extend admin list search (edit.php) to match whitelisted post meta values.
 */
add_filter('posts_search', 'tqb_admin_search_meta', 10, 2);
function tqb_admin_search_meta(string $search, WP_Query $query): string {
    global $wpdb, $pagenow;

    if (!is_admin() || $pagenow !== 'edit.php') {
        return $search;
    }

    $term = $query->get('s');
    if (!is_string($term) || $term === '') {
        return $search;
    }

    $meta_keys = tqb_admin_search_meta_keys();
    if ($meta_keys === []) {
        return $search;
    }

    $like = '%' . $wpdb->esc_like($term) . '%';
    $key_placeholders = implode(', ', array_fill(0, count($meta_keys), '%s'));
    $meta_clause = $wpdb->prepare(
        " OR ({$wpdb->posts}.ID IN (
            SELECT post_id FROM {$wpdb->postmeta}
            WHERE meta_key IN ($key_placeholders) AND meta_value LIKE %s
        ))",
        ...array_merge($meta_keys, [$like])
    );

    if ($search !== '') {
        $search = preg_replace('/\)\s*$/', $meta_clause . ')', $search, 1);
    } else {
        $search = $wpdb->prepare(
            " AND ({$wpdb->posts}.ID IN (
                SELECT post_id FROM {$wpdb->postmeta}
                WHERE meta_key IN ($key_placeholders) AND meta_value LIKE %s
            ))",
            ...array_merge($meta_keys, [$like])
        );
    }

    return $search;
}

如果要新增搜索关键词

add_filter('tqb_admin_search_meta_keys', function ($keys) {
    $keys[] = 'inner_diameter';
    return $keys;
});