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
*
* https://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' );
It worked perfect, Thanks!
It worked perfectly. Thank you, you’re the best! :)
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
I echo the two previous comments. Your code worked perfectly.
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!
Thanks it very helpful for Me…
:-):-):-)
Perfect, useful, well-done. Thank you!
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!
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.
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 !!! :)
Thanks!
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!
Awesome. Just copy/pasted it into the functions.php and it worked. Thanks for the docu too :)
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?
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.
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).
Worked perfectly with ACF – thank you!
Hi Nikki,
Could you take 1 minute and explain me what is the next step once the code is in functions.php ?
I am using ACF as well with some fields but don’t get the point on how to implement that.
Thank you
Nicolas
Perfect, useful, well-done. Thank you!
You Sir..have just saved me hours. Great bit of code and works perfectly. THANK YOU.
Just came here to say: DUDE!
Awesome. Exactly what I was looking for.
Thank you.
Thank You!!
It works perfectly, I just pasted the code to function.php.
Have a great day!
Hi – this is super helpful! However, it doesn’t work if you are trying to Load More results via AJAX – any thoughts on how to search all the meta fields when using AJAX?
Thanks for this snippet Adam.
I got one issue though. When I search for a meta value my pagination doesn’t work, I get a 404 on pages above page 1. The results are perfectly fine on the first page.
If I search for a string that exists in both the posts- and postmeta-table my pagination indicates 6 pages (for example) on the first search-results-page. When navigating to page 2 the pagination indicates 2 pages, so it seems that the meta value isn’t included in the paginated results on other pages than the 1st.
Do you know of any solution for this problem?
Thanks a lot mate, works like a charm!
One thing I’d like to share, when I try to filter WooCommerce products at the back-end, based on a custom field, every time the URL contains “?s” (which means it’s a search query), the result is always empty. So I had to put a
( ! is_admin() )
check at your codes, to make sure this only happens at the front-end. I only needed this at the front-end anyway, so no problem for me.
Cheers.
Very useful, thank you very much !!
Its working and so much……:D
Hi Adam.
I used your code and it was working great, but it is not working from wp version4.9.x and the latest 5.0.3.
Any chance you can update this code, please?
Many thanks.
Had to change these two lines to get it working on WP 5.8.1 as I had an error ( Not unique table/alias: ‘wp_postmeta’ )
$join .=’ LEFT JOIN ‘.$wpdb->postmeta. ‘ ON ‘. $wpdb->posts . ‘.ID = ‘ . $wpdb->postmeta . ‘.post_id ‘;
to
$join .=’ LEFT JOIN ‘. $wpdb->postmeta . ‘ AS post_metas ON ‘ . $wpdb->posts . ‘.ID = post_metas.post_id ‘;
and
“(“.$wpdb->posts.”.post_title LIKE $1) OR (“.$wpdb->postmeta.”.meta_value LIKE $1)”, $where );
to
“(“.$wpdb->posts.”.post_title LIKE $1) OR (post_metas.meta_value LIKE $1)”, $where );
Hope to help someone ;D