Nested meta queries with WP_Meta_Query

WP_Meta_Query

If you’re working with complex data structures in WordPress, you’ll be pleasantly surprised by the new nested query capabilities WP_Meta_Queryin version 4.1.

We previously wrote about custom fields in WP_Query, where we looked at several examples of working with the WP_Querymeta_query class parameter. This option allows you to select records by their metadata, which can be useful for a product catalog, a real estate agency website, etc.

In the new version of WordPress 4.1 (which will be released in December 2014), this option allows you to perform nested queries for more complex data fetching. To understand how this works, it’s easiest to look at an example.

Suppose we choose a car. If the price of the car is more than $5,000, then we agree only on the red color of the body, but if the car is under $5,000, then we are ready to consider other colors: red, green, blue, and yellow. With nested structures in WP_Meta_Queryour query would look like this:

$query = new WP_Query(array(
'meta_query' => array(
'relationship' => 'OR',
array(
'relationship' => 'AND',
array(
'key' => 'price',
'value' => 5000,
'type' => 'numeric',
'compare' => '>=',
),
array(
'key' => 'color',
'value' => 'red',
),
),
array(
'relationship' => 'AND',
array(
'key' => 'price',
'value' => '5000',
'type' => 'numeric',
'compare' => '<',
),
array(
'key' => 'color',
'value' => array( 'red', 'green', 'blue', 'yellow' ),
'compare' => 'IN',
),
),
),
) );

Here, in the parameter, meta_querywe specify two nested queries with a relationship (relation) OR. This means that the selection will include records that satisfy the requirements of at least one of the two queries specified below in the arrays.

First request:

array(
'relationship' => 'AND',
array(
'key' => 'price',
'value' => 5000,
'type' => 'numeric',
'compare' => '>=',
),
array(
'key' => 'color',
'value' => 'red',
),
),

It has a nested structure with two subqueries and a relation AND, i.e. to be included in the selection of this query, both of its subqueries must be executed: the price of the car is equal to or greater than 5000, and the color is red.

Also see: Editing Thumbnails in WordPress with the Post Thumbnail Editor Plugin

Second request:

array(
'relationship' => 'AND',
array(
'key' => 'price',
'value' => '5000',
'type' => 'numeric',
'compare' => '<',
),
array(
'key' => 'color',
'value' => array( 'red', 'green', 'blue', 'yellow' ),
'compare' => 'IN',
),
),

It also has a nested structure and two subqueries with the relation AND selects all cars with a cost less than 5000 and a red, green, blue, or yellow body color.

Thus, nested structures in the WP_Meta_Querythe WordPress database give us the ability to perform more complex selections on meta fields in the WordPress database. It is worth noting that a similar syntax is also available for classes WP_Tax_Queryand WP_Date_Query. Prior to version 4.1, such queries had to be built independently in the SQL language, for example, using a filter posts_clauses.

Also see: Editing Thumbnails in WordPress with the Post Thumbnail Editor Plugin

Despite the enhanced capabilities of querying metadata in 4.1, you should not forget about the performance of the database. After all, the field type meta_valuein the table wp_postmetais text, and converting it to a numeric or any other type during search and comparison means that the MySQL server needs to scan all rows with a specific key, without the possibility of using additional auxiliary indexes.

For a large amount of data, such queries can become slow, and we recommend looking towards external indexing and data search systems, such as Sphinx or Elasticsearch.

Leave a comment

Your email address will not be published. Required fields are marked *