5

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.

6

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.

  • Interesting usage of spl_object_hash to remove filters with anonymous function callbacks +1 – birgire Oct 10 '14 at 12:37
  • Thanks @birgire, as said in loopchat, that's the main reason why I posted this answer :) – gmazzap Oct 10 '14 at 13:48
  • So, if we have say 3 array of meta query and I put the balance query on the third one I should use mt3.meta_value instead? – Sisir Oct 13 '14 at 4:49
  • 1
    @Sisir mt2.meta_value. The first meta is not aliased, is called with real name wp_postmeta if "wp" is table prefix. the second is mt1 (as in the example), third is mt2 and so on. However I have to say that with more than 2 meta ket this method is not really affordable... – gmazzap Oct 13 '14 at 5:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.