Adam J. Balée

Search WordPress by Custom Fields without a Plugin

wp-dkblue-orange-1080x675

Custom fields are one of the more powerful features available in WordPress. They are particularly useful when extending WordPress via the use of custom post types. I create custom post types all the time for things like products, portfolios, or galleries when developing WordPress themes for clients. Custom fields are extremely handy when it comes to adding product details, such as item numbers or prices. Unfortunately, it’s not possible to search WordPress by custom fields out of the box. In order to fix that, we need to modify the WordPress search query to include custom fields.

Left Join

The ‘postmeta’ table is where all the custom field data is stored in the database. By default, the WordPress search functionality is set to search the ‘posts’ table only. In order to include the custom fields data in our search, we first need to perform a left join on the ‘posts’ and ‘postmeta’ tables in the database.

/**
 * Join posts and postmeta tables
 *
 * http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_join
 */
function cf_search_join( $join ) {
    global $wpdb;

    if ( is_search() ) {    
        $join .=' LEFT JOIN '.$wpdb->postmeta. ' ON '. $wpdb->posts . '.ID = ' . $wpdb->postmeta . '.post_id ';
    }
    
    return $join;
}
add_filter('posts_join', 'cf_search_join' );

Modify the Query

Next we need to modify the WordPress search query to include custom fields.

/**
 * Modify the search query with posts_where
 *
 * http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_where
 */
function cf_search_where( $where ) {
    global $pagenow, $wpdb;
   
    if ( is_search() ) {
        $where = preg_replace(
            "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
            "(".$wpdb->posts.".post_title LIKE $1) OR (".$wpdb->postmeta.".meta_value LIKE $1)", $where );
    }

    return $where;
}
add_filter( 'posts_where', 'cf_search_where' );

Prevent Duplicates

Finally, we need to add the DISTINCT keyword to the SQL query in order to prevent returning duplicates.

/**
 * Prevent duplicates
 *
 * http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_distinct
 */
function cf_search_distinct( $where ) {
    global $wpdb;

    if ( is_search() ) {
        return "DISTINCT";
    }

    return $where;
}
add_filter( 'posts_distinct', 'cf_search_distinct' );

Wrapping It Up

Add the following to functions.php to start searching WordPress by custom fields. Not only will this code modify the search on the front-end, but you’ll also be able to search the edit screens in the admin by custom fields as well.

<?php
/**
 * Extend WordPress search to include custom fields
 *
 * http://adambalee.com
 */

/**
 * Join posts and postmeta tables
 *
 * http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_join
 */
function cf_search_join( $join ) {
    global $wpdb;

    if ( is_search() ) {    
        $join .=' LEFT JOIN '.$wpdb->postmeta. ' ON '. $wpdb->posts . '.ID = ' . $wpdb->postmeta . '.post_id ';
    }
    
    return $join;
}
add_filter('posts_join', 'cf_search_join' );

/**
 * Modify the search query with posts_where
 *
 * http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_where
 */
function cf_search_where( $where ) {
    global $pagenow, $wpdb;
   
    if ( is_search() ) {
        $where = preg_replace(
            "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
            "(".$wpdb->posts.".post_title LIKE $1) OR (".$wpdb->postmeta.".meta_value LIKE $1)", $where );
    }

    return $where;
}
add_filter( 'posts_where', 'cf_search_where' );

/**
 * Prevent duplicates
 *
 * http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_distinct
 */
function cf_search_distinct( $where ) {
    global $wpdb;

    if ( is_search() ) {
        return "DISTINCT";
    }

    return $where;
}
add_filter( 'posts_distinct', 'cf_search_distinct' );
?>

19 Comments

  1. GerardoReply

    It worked perfect, Thanks!

  2. DanielReply

    It worked perfectly. Thank you, you’re the best! :)

  3. WebmastaReply

    Works perfect with ACF (Advanced Custom Fields) 4.x and latest WordPress 4.2.2 here.

    Thanks a lot, you should publish this as a plugin at wordpress.org

  4. MikeReply

    I echo the two previous comments. Your code worked perfectly.

  5. JohnReply

    2 days, two days, TWO DAYS!!!! of searching for a function that should be inside the wordpress core!!!!
    THANKS!!!!
    PS Is there a way to extend it to categories?
    Example: if there is a category “Cars” and the user types the word “car” in search field, it will return all posts inside category “Cars” (even if the word “car” is never mentioned in the post title or inside the content of the post).
    Thanks again for your precious help!

  6. ShivaReply

    Thanks it very helpful for Me…
    :-):-):-)

  7. Aaron SilverReply

    Perfect, useful, well-done. Thank you!

  8. GaldReply

    Hi man,

    I have been searching for this for days, and finally you got it! Even I posted twice in wordpress.org forums without any response. Great!!!

    Thank you so much!

  9. CarstenReply

    Thank you, great code! The only problem occurs when I try to to search two custom fields using the standard (?) AND operator. A single search per custom field works fine, but not for two search terms coming from two different custom fields.

  10. PaulReply

    This is a great solution to a problem I had trying to search Custom Fields on my business directory. Like the other guys said, I’ve spent days trying to find something as simple as this without having to drill down into the core files and the fact that I can just add this to the functions.php is bloody marvelous.

    Thanks so much !!! :)

  11. Robin MajumdarReply

    Brilliant, works like a charm.

    With your , as well as full credit and attribution , I’d like to translate your article in French on my tech blog?

    One note, readers should edit the functions.php file in their themes folder.

    Thanks again!

  12. ggzoneReply

    Awesome. Just copy/pasted it into the functions.php and it worked. Thanks for the docu too :)

  13. DaveReply

    This is great thanks. Was just wondering, is it possible to order the search results so that it lists posts from a specific post_type first? At the moment they’re in date order so would be fantastic if I could order them by the most relevant post_types?

  14. adam roweReply

    This is great! I’m using this in conjunction with updating a custom field with all of a post’s terms to allow WP’s search to find tags and categories.

  15. RyanReply

    Thank you very very much. I am using the Advanced Custom Fields Plugin on a fairly large website and this just saved me an unknown amount of time (but it would have been alot).

  16. NikkiReply

    Worked perfectly with ACF – thank you!

  17. YankoReply

    Awesome post. Thank you

Leave a comment