Recently I was tasked with a project that required some filtering of a custom post type based on values stored using the WordPress Metadata API. Due to how popular it is to use metadata for storing additional data for posts (and custom post types) I thought it would be useful to share some of the details of how filtering on this metadata would work.

In this article, I will be referring mostly to posts but the theory applies to custom post types as well.

A short introduction to metadata

In a WordPress environment, metadata is a way of storing and retrieving additional pieces of information on a specific WordPress object, without needing to register additional tables. The simplest example of this is the ability to add Custom Fields to a WordPress post.

When created, these custom fields are stored in the post metadata table, which (assuming a default table prefix of wp_ ) is stored in the wp_postmeta table. WordPress plugin developers typically use metadata for storing custom field values as it does not require making any changes to the database to function.

The WP_Query object: what is it and what can you do with it?

To be able to filter by metadata, you first need to meet the WP_Query object.

The WP_Query object deals with the intricacies of entity requests to a WordPress website. In fact, the most common place where you might encounter and interact with WP_Query is within The Loop, when displaying a list of posts or pages

What WP_Query does is determine what type of query it's dealing with, and fetches the requested posts. Think of a category archive, dated archive, feed, or search as good examples.

It also retains a lot of information on the request, which can be pulled at a later time, for example, when building pagination.

Using the WP_Query object allows you to create and access a custom filtered collection of posts, based on a set of specified arguments. Because of this, it allows you to display these posts and their associated data whichever way you require.

Since all default entity data within WordPress is based on posts (pages, products, any custom post types) the WP_Query object is probably the most used class in the entirety of a WordPress website.

The most common page you would see WP_Query in action would be on your blog page. There it is retrieving a list of posts, limiting how many are displayed (usually by 10) and returning the total number of posts so that the pagination function can determine what pagination links to display.

Need help for your next WordPress project? Hire Jonathan Bossenger and start working with him immediately!

Using Post Parameters and WP_Query to filter your post/post_type results

Let’s look at some simple WP_Query examples of how you could filter your posts or post types by their parameters.

$args = array( 'posts_per_page' => 10 ); $the_query = new WP_Query( $args );

This is a pretty simple query, passing an array of arguments which only contains the ‘posts_per_page’ argument. This query should get all posts but limit the number of posts to 10.

So let’s look at what the actual SQL query for this query would look like.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'expired' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'expired' OR wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 10

While the larger details of the actual SQL query might be a little technical, it’s pretty neat to see all the things that WP_Query does by default ( using SQL_CALC_FOUND_ROWS , checking against things like post_status, ordering by post_date )

So let’s extend our WP_Query arguments a little further.

$args = array( 'posts_per_page' => 10, 'orderby' => 'title', 'order' => 'ASC' );

So now we would expect the query results to be ordered by the title field and in ascending order.

Sure enough, the SQL query has been updated:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'expired' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'expired' OR wp_posts.post_status = 'private') ORDER BY wp_posts.post_title ASC LIMIT 0, 10

Pretty standard stuff, but interesting to see how the power of WP_Query by simply applying a few query arguments.

The WP_Query documentation on the WordPress Codex contains all the different types of available query parameters. It’s quite an extensive and very useful document to read.

Using Custom Field Parameters to filter your post/post_type results by post meta key/values

So far I’ve been focusing on parameters that are directly tied to posts, in that they represent fields in the wp_posts table. Now we need to look a little deeper and understand how to filter posts based on metadata.

Let’s start with a simple example.

You have a custom post type called Product (which has a slug of ‘product’), and it has a custom field called ‘color’ in which you store a single word color string (e.g. white, yellow, blue) for that product. You now want to retrieve all Products where the color is blue.

Extending the query above your WP_Query arguments would now look like this:

$args = array( 'post_type' => 'product', 'posts_per_page' => 10, 'orderby' => 'title', 'order' => 'ASC', 'meta_key' => 'color', 'meta_value' => 'blue' );

The first thing to notice is how we have added a ‘post_type’ argument so that we only filter on Product post types. The second thing we have added is the meta_key and meta_value arguments. I would now expect to see the query being updated to filter by products only and to include a join to the metadata table where the metadata is stored and a filter by the meta_key ‘color’:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'color' AND wp_postmeta.meta_value = 'blue' ) ) AND wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'expired' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'expired' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_title ASC LIMIT 0, 10

Sure enough, there it is.

So let’s take it a little further. Let’s say you had ‘available_from_date’ and ‘available_to_date’ meta keys set on your Products. You wanted to create a search form where users could search for Products that are ‘available’ within those dates. The user would be choosing the from and to date fields and you wanted to filter your posts based on these fields. You can’t simply update your meta_key and meta_value fields in the query arguments, you would need to use a meta_query array of arguments, to further customise your query.

$args = array( 'post_type' => 'product', 'posts_per_page' => 10, 'orderby' => 'title', 'order' => 'ASC', 'meta_query' => array( 'relation' => 'AND', array( 'key' => 'available_from_date', 'value' => array( $from_date, $to_date ), 'compare' => 'BETWEEN', 'type' => 'DATE', ), array( 'key' => 'available_to_date', 'value' => array( $from_date, $to_date ), 'compare' => 'BETWEEN', 'type' => 'DATE', ), ) );

(Note in the example above the $from_date and $to_date variables should have already been parsed from the user’s form submission)

Let’s see what’s happening on an SQL level.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'available_from_date' AND CAST(wp_postmeta.meta_value AS DATE) BETWEEN '2017-10-01' AND '2017-10-01' ) AND ( mt1.meta_key = 'available_to_date' AND CAST(mt1.meta_value AS DATE) BETWEEN '2017-10-01' AND '2017-10-01' ) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'expired' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'expired' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_title ASC LIMIT 0, 10

That’s quite a lot to take in but in short, it’s adding the following to the query:

Casting the meta_key values as dates (usually stored as text) to allow the date filtering to happen Filtering posts where the available_to_date key is between the two dates sent Also filtering posts where the available_to_date key is between the two dates sent

That’s a pretty hefty SQL query for just adding one extra nested array of arguments to your query arguments!

Why you should get to know WP_Query better

Most of the time, when building a WordPress website, you’ll never use or need to worry about the WP_Query object. If you are building a theme, a child theme (or a simple plugin), you might need to use WP_Query to retrieve and list your posts or pages, but only if you really need some deep customisation. Mostly the WP_Query object is already in scope and a simple ‘if ( have_posts() )’ check is all you need to use The Loop.

However, if you are building something more specific (a more advanced plugin using custom post types or child theme) that needs to do more than just retrieve and list posts or pages, at some point, you will need to use the WP_Query object.

Knowing (or just hiring someone who knows) how to use the WP_Query object will save hours off your development time and improve not only your product’s code, but also ensure that it confirms to WordPress standards and practices.

This also means that if you ever need to hire additional WordPress developers to continue to work on your product, they’ll be able to pick up and update your queries in a flash.

Let’s go back to that original metadata example I posted above (the one with the date filtering). Imagine if you had to build that query and generate its results, without using WP_Query.

global $wbdb; $query = "SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'available_from_date' AND CAST(wp_postmeta.meta_value AS DATE) BETWEEN $from_date AND $to_date ) AND ( mt1.meta_key = 'available_to_date' AND CAST(mt1.meta_value AS DATE) BETWEEN $from_date AND $to_date ) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'expired' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'expired' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_title ASC LIMIT 0, 10" $results = $wpdb->get_results( $query, OBJECT );

Using WP_Query, instead of having to type (and test) all that SQL, your code could look like the example below:

$args = array( 'post_type' => 'product', 'posts_per_page' => 10, 'orderby' => 'title', 'order' => 'ASC', 'meta_query' => array( 'relation' => 'AND', array( 'key' => 'available_from_date', 'value' => array( $from_date, $to_date ), 'compare' => 'BETWEEN', 'type' => 'DATE', ), array( 'key' => 'available_to_date', 'value' => array( $from_date, $to_date ), 'compare' => 'BETWEEN', 'type' => 'DATE', ), ) ); $results = WP_Query( $args );

Two different ways to get the same outcome, and I know which one I'd prefer to do.

Need help for your next WordPress project? Hire Jonathan Bossenger and start working with him immediately!