How to Compare Two Meta Fields

by

Consider two meta fields balance and monthly_price. How you would get all posts that has balance < monthly_price

Thinking of creating meta query but our both fields are arbitrary or do not have specific value.

1
1

A rought method would be to set value of balance to 'mt1.meta_value' and then use a 'posts_request' filter to remove the escaping quotes:

$args = array(
  'post_type' => 'post', // maybe not
  'meta_query' => array(
    array(
      'key' => 'balance',
      'compare' => '>=',
      'value' => 'mt1.meta_value', // this will be treated as a string
      'type' => 'NUMERIC'
    ),
    // this is to force adding the needed JOIN clause
    array(
      'key' => 'monthly_price', 
      'compare' => 'EXISTS' 
    ),
    'relation' => 'AND'
  )
);

$closure = function( $sql ) {
  // remove single quotes around 'mt1.meta_value'
  return str_replace( "'mt1.meta_value'", "mt1.meta_value", $sql );
};

add_filter( 'posts_request', $closure );

$query = new \WP_Query( $args ); // this is your query :)

remove_filter( 'posts_request', spl_object_hash( $closure ) );

I should add that I used 'mt1.meta_value' because WordPress when multiple meta queries are in the args use table aliases like "mt{$n}" where $n is incremented for each additional query.

Leave a Comment