WP_Query: How to Compare Meta Fields values in meta_query Request
The WP_Query out of the box does not allow comparisons between the values of meta-fields with which we are working.
For meta-fields in the compare
you can specify BETWEEN, >, >=, <, <=. But in the value parameter, we must specify some specific data. And if we need to specify the value of another meta-field for comparison, then WP is powerless here and we will have to change the query itself, which can be painful for inexperienced users and possibly an unsolvable task.
Example of when this might be useful.
In the product card, two meta fields are specified. (Floor "room_floor" and Maximum floor in the building "room_floor_max".)
For filtering objects, the selection is based on the number of floors ("Not the first floor" - works specifically because I pass the value of the 1st floor to the value)
Question: How can I adjust the processing of the last floor? Essentially, I need to compare two meta fields ("room_floor" and "room_floor_max"), if they are the same, then I do not display them. For example, there is an object on the 5th floor out of 5 maximum (5/5), this object is suitable for filtering.
This problem can be solved as follows (you can find something similar on the "internet"):
$args = [ 'post_type' => 'post', 'posts_per_page' => 50, 'meta_query' => [ 'relation' => 'AND', // wp_postmeta.meta_value [ 'key' => 'room_floor', 'value' => 1, 'compare' => '!=', 'type' => 'NUMERIC', ], // mt1.meta_value [ 'key' => 'room_floor_max', 'value' => 'wp_postmeta.meta_value', 'compare' => '!=', 'type' => 'NUMERIC', ], ], ]; $closure = function( $clauses ) { //$clauses['where'] = preg_replace( "/'mt(\d+)\.meta_value'/", 'mt$1.meta_value', $clauses['where'] ); $clauses['where'] = str_replace( "'wp_postmeta.meta_value'", 'wp_postmeta.meta_value', $clauses['where'] ); return $clauses; }; add_filter( 'posts_clauses', $closure ); $my_posts = get_posts( [ 'suppress_filters' => false ] + $args ); remove_filter( 'posts_clauses', $closure ); foreach( $my_posts as $pst ){ echo "$pst->post_title\n"; }
Here, in the value, we specify 'wp_postmeta.meta_value'
- this is the column name that will ultimately be used in the SQL query. But this name will be used in the query as a string, and we need to use it as is, for this, on the posts_clauses_request hook, we modify the request and turn the string 'wp_postmeta.meta_value'
into the actual part of the request wp_postmeta.meta_value
.
Universal Option
I recommend using this option. Because it:
- Is more understandable when used.
- Convenient - because you don't have to find out what prefix the meta-field has in the query.
- Stable - when changing/extending the meta_query parameters, what already worked will not break.
The approach shown above can be made more universal - give a name to the array specified in the meta_query
and use this name in another meta_query
array, for the value
parameter. Then replace this name with the actual column name that resulted from the query assembly. The advantage of this approach is that you don't need to know the specific prefix of the meta_value
column (which can be: mt1, mt2, wp_postmeta, or some other).
Another fragile point is when changing the code (query parameters). For example, when adding another array to meta_query
or changing the relation to OR, the prefix mt1
may change in the resulting SQL query and your code will stop working.
The code below is devoid of all these shortcomings! And the query parameters become more understandable.
By the way, a similar approach is already used in the orderby parameter. It allows you to sort the query by a specific meta-field from the meta_query
parameter.
/** * Allows to use `meta_query` parameter item key as `value` * parameter in another `meta_query` item for compare meta * values between each other. * * Example: * * 'meta_query' => [ * 'relation' => 'AND', * 'room_floor.value' => [ * 'key' => 'room_floor', * 'value' => 1, * 'compare' => '!=', * 'type' => 'NUMERIC', * ], * [ * 'key' => 'room_floor_max', * 'compare' => '!=', * 'value' => 'room_floor.value', * 'type' => 'NUMERIC', * ], * ], * * // in this example we specified `room_floor.value` key * // and then use it as `value` for another item. * * @requires PHP 7.0 */ final class WP_Query_Allow_Postmeta_Compare { public static function init(){ add_filter( 'posts_clauses', [ __CLASS__, '_meta_value_replacer' ], 20, 2 ); //add_filter( 'posts_request', [ __CLASS__, 'debug_die_request' ], 999 ); } public static function _meta_value_replacer( $clauses, $wp_query ){ /** @var WP_Meta_Query $mq */ $mq = $wp_query->meta_query; if( ! $mq ){ return $clauses; } $mq_clauses = $mq->get_clauses() ?: []; $replace = []; foreach( $mq_clauses as $key => $clause ){ if( $clause['key'] === $key ){ trigger_error( "`Meta clause key` can not be the same as value parameter. The key: $key" ); continue; } $value = $clause['value'] ?? ''; $the_clause = $mq_clauses[ $value ] ?? []; if( ! $the_clause ){ continue; } $from = "'$value'"; if( 'CHAR' === $the_clause['cast'] ){ $to = sprintf( '%s.meta_value', $the_clause['alias'] ); } else { $to = sprintf( 'CAST( %s.meta_value AS %s )', $the_clause['alias'], $the_clause['cast'] ); } $replace[ $from ] = $to; } foreach( $replace as $from => $to ){ $clauses['where'] = str_replace( $from, $to, $clauses['where'] ); } return $clauses; } public static function debug_die_request( $sql ){ die( $sql ); } }
How to use?
Connect the class code somewhere (preferably in a file and include the file in functions.php). Then somewhere in functions.php, start the class (it will not affect other queries because they will not meet the conditions):
WP_Query_Allow_Postmeta_Compare::init();
Now in the get_posts() or WP_Query requests, in the meta_query parameter, you can specify a key for a separate item and use this key in the value of another item to compare the values of meta-fields according to the specified compare
condition. Also, the type
parameter is taken into account, for example, if it is NUMERIC, the values will be compared as numbers.
Request example:
$args = [ 'post_type' => 'post', 'posts_per_page' => 50, 'meta_query' => [ 'relation' => 'AND', 'room_floor.value' => [ 'key' => 'room_floor', 'value' => 1, 'compare' => '!=', 'type' => 'NUMERIC', ], [ 'key' => 'room_floor_max', 'compare' => '!=', 'value' => 'room_floor.value', 'type' => 'NUMERIC', ], ], ]; $my_posts = get_posts( [ 'suppress_filters' => false ] + $args ); foreach( $my_posts as $pst ){ echo "$pst->post_title\n"; }
Here we specified an arbitrary key room_floor.value
for the array item, and then used it in the value of another item: 'value' => 'room_floor.value'
.
Critically important points:
-
The name of the key (in the example
room_floor.value
) should not coincide with the name of the meta-field itself (in the exampleroom_floor
)! - The
suppress_filters
query parameter must befalse
. Because this hack works on hooks and ifsuppress_filters=true
, the necessary hooks will not be triggered. By default, in the get_posts() function, it istrue
.
For WP_Query, it is not necessary to specify suppress_filters=false
.
$query = new WP_Query( $args ); if ( $query->have_posts() ) { while ( $query->have_posts() ) { $query->the_post(); ?> <li><?php the_title() ?></li> <?php } } else { // No posts found } wp_reset_postdata(); // Reset $post.