Searching custom meta on custom post types

This post is more than 7 years old.

Ever needed to search for a post by something other than the title or post content? If you’ve built a website with custom post types that have additional fields, it’s a good chance that admins will need to search on your custom meta.

There’s a little trick to getting that to work. WordPress has a filter hook called posts_search that lets you change the SQL for the post search query, but not until it’s already built it. I could rebuild the search clause, but I prefer to use a regular expression to inject extra clauses into what WordPress already built. That way, if there’s another plugin adding things, I don’t stomp all over what it added.

Since this is for the admin listing of posts, I also only want to add the filter when in the admin, and only on the page for my post type. In the example code, I’ll use a post type called example_type; you can change that to whatever your post type is called.

For the regular expression, I’ve used the \K operator. I like using this operator because it lets me inject strings into other strings without the expense of using the regular expression capture buffer. Why capture something and add it back to itself, if you can just find the end of it and inject your string after that? Simples.

There’s a little trick to the query too. If you add to the search clause by making additional table joins, you’ll probably end up with duplicates in your search, unless you also add a distinct to your query. No. Much simpler to use an exists subquery. Less code, less messing around, less hassle.

You probably have more than one item of custom meta that you want to search too. Rather than building a subquery for each one, why not just query the lot of them in one hit in the same subquery? That’s what the in operator is for. You could also modify the subquery to use like instead, and match on any meta keys that match your naming pattern. NB: if you do that, remember to escape any underscores with a backslash because underscore is a wildcard in like and will wreck an index lookup!

Putting it all together, here’s all the code you need for adding custom meta into admin post searches.

add_action('admin_init', 'admin_init_example_type');

/**
* hook the posts search if we're on the admin page for our type
*/
function admin_init_example_type() {
    global $typenow;

    if ($typenow === 'example_type') {
        add_filter('posts_search', 'posts_search_example_type', 10, 2);
    }
}

/**
* add query condition for custom meta
* @param string $search the search string so far
* @param WP_Query $query
* @return string
*/
function posts_search_example_type($search, $query) {
    global $wpdb;

    if ($query->is_main_query() && !empty($query->query['s'])) {
        $sql    = "
            or exists (
                select * from {$wpdb->postmeta} where post_id={$wpdb->posts}.ID
                and meta_key in ('_example_type_meta_1','_example_type_meta_2')
                and meta_value like %s
            )
        ";
        $like   = '%' . $wpdb->esc_like($query->query['s']) . '%';
        $search = preg_replace("#\({$wpdb->posts}.post_title LIKE [^)]+\)\K#",
            $wpdb->prepare($sql, $like), $search);
    }

    return $search;
}

And finally, the job of getting a blog post out in 2016 is done! :tada: