Get Posts by Custom Fields in WordPress - Part 2

In part 1 of this series, we learned about how to get posts by custom fields in WordPress with the WP_Query function. However, if we need to deal with databases, it’s better to find another faster, easier, and more convenient way. That method is joining wp_posts and wp_postmeta tables together, and then querying by SQL. In part 2 of the series “How to get posts by custom fields in WordPress”, we’ll use this way to add the advanced searching feature that allows users to search by keywords in posts / pages titles and custom fields content.

Table of Contents hide

1. Before Getting Started
2. Step 1: Create Custom Fields for Posts
3. Step 2: Create the Advanced Searching Feature
4. Last Words

Before Getting Started

In this article, we need to install and activate these tools:

Meta Box: A framework supports creating and editing custom fields in WordPress easily and quickly. You can download the free Meta Box core plugin on wordpress.org.
Meta Box Builder: A premium extension of Meta Box plugin that supports creating, editing, and managing custom fields with an intuitive user interface right on the back end without coding. Alternatively, you can use the Online Generator tool of Meta Box to get a total free UI for creating custom fields.
MB Admin Columns: A premium extension of Meta Box plugin that supports displaying custom fields on the post list on the front end, thus helping you manage and organize posts by custom fields easily and quickly.

Step 1: Create Custom Fields for Posts

Assuming that we have a blog about dishes, and each dish is a post so we need to classify them into different types of dishes using custom fields. Here is an example:

Create Custom Fields named Type for Posts in WordPress website

First, on the Admin Dashboard, go to Meta Box > Custom Fields and click Add New.

Add a custom field named Type for posts in WordPress website with Meta Box Builder

Click Add New, select and add a new Text field, fill in the Title, ID, and Label boxes, and then click Publish. In this example, I create a custom field named Type.

Create the custom field Type for posts in WordPress website

Next, we need the MB Admin Columns extension to display custom fields before post names on the All Posts section. Thus, you can manage posts by custom fields more easily. However, it’s optional.

Tick to Show as an admin column, select the Column position as After title.

Edit the custom attribute to display it before the post name in All Posts section

Finally, go to the Settings tab. In the Show for and Post types sections, choose Post to display the Type custom field to every post on the WordPress website. And don’t forget to click Update!

Display custom field Type on every post on WordPress website

We’ve finished creating a new custom field named Type and add this custom field to every post on the WordPress website. Now when you go to a post and drag to the end, you will see the Type field. Now, you just need to fill in the value for this field.

Fill in the value for the custom field Type in each posts

After that, go to Posts > All Posts, you will see the custom field values listed on the Type column.

Custom fields are displayed before the post names on the back end — Custom fields are displayed before the post names on the post list

To create the advanced searching feature that allows users to search by custom field values, move to step 2.

Step 2: Create the Advanced Searching Feature

As mentioned above, WordPress only supports searching for keywords in the titles and content of posts and pages. To search for posts by keywords in the posts / pages titles and custom field values, you need to understand how the WordPress search feature works first.

By default, WordPress only allows searching by keywords on the wp_posts table in the database. Meanwhile, the wp_postmeta table stores additional data like custom fields’ values.

Thus, to search by custom field values, we need to JOIN wp_posts and wp_postmeta tables together. This work can be done by adding this code to the functions.php file:

function justread_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', 'justread_search_join' );

Note: justread is the theme I’m using (you can download this free WordPress theme here).

Next, you have to edit the query to search for posts by custom field values. To do it, add the following code to the functions.php file:

function justread_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', 'justread_search_where' );

From now on, you can search by custom field values. However, in the search results, there are some revisions that can make it difficult for users to choose the right result. Therefore, it’s crucial to delete these revisions by adding the code including the DISTINCT keyword to the functions.php file

function justread_search_distinct( $where ) {
    global $wpdb;

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

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

Now, the new feature to search by custom field values is complete.

To check if this search works well or not, I tried searching by the “breakfast” keyword. In the search results, except for one post that contains the “breakfast” keyword on its title (Lite breakfast with bread), there are two different posts with the “breakfast” keyword in the Type custom field. It means the advanced searching feature worked properly.

The advanced search feature worked well

Last Words

Joining wp_posts and wp_postmeta tables together and editing the query will save more time and effort than the first method of this series. The reason is that we will query by SQL and don’t need to create a page template.

Getting posts by custom field can be utilized to create an advanced search that helps users find their desired results quickly, easily, and appropriately. You should absolutely use this feature to improve the user experience! Besides, you may like this article on how to create an advanced search box in WordPress.