Compare two meta-fields in a wp_query (where meta-field-A is larger than meta-field-B)

Question

I have a custom post type (foobar), with two meta_fields:

• updated_at
• build_ran_at

I would like to make a single WP_query, that returns on the foobar-posts, where updated_at is after build_ran_at. Both the fields are DateTime-fields.

---

Solution attempt

$foobar_query = new WP_Query([
  'post_type' => 'foobar',
  'post_status' => 'publish',
  'posts_per_page' => 999,
  'meta_query' => [
    'relation' => 'AND',
    [
      'key' => 'updated_at',
      'value' => ...? 
      'compary' => ...?
    ],
    [
      'key' => 'build_ran_at',
      'value' => ...? 
      'compary' => ...?
    ],
  ]
]);

$returned_posts = [];
if( $foobar_query->have_posts() ):
  while( $foobar_query->have_posts() ):
    $foobar_query->the_post();
    $returned_posts[] = get_post();    
  endwhile; // while( $foobar_query->have_posts() ):
  wp_reset_query();
endif; // if( $foobar_query->have_posts() ):

Answer 1

As of writing, there is no meta query compare value that can do what you're trying to do, which is basically "where <updated_at meta> > <build_ran_at meta>". But there are two options that you can choose from as an alternative to using the meta_query arg:

1. Use a raw SQL to retrieve just the IDs of the posts having the updated_at meta greater than the build_ran_at meta, and then pass the IDs to WP_Query via the post__in arg like so:

   // Build the raw SQL.
   $query = "
       SELECT p.ID
       FROM $wpdb->posts p
           INNER JOIN $wpdb->postmeta pm ON pm.post_id = p.ID
           INNER JOIN $wpdb->postmeta pm2 ON pm2.post_id = p.ID
       WHERE p.post_type = 'foobar'
           AND p.post_status = 'publish'
           AND pm.meta_key = 'updated_at'
           AND pm2.meta_key = 'build_ran_at'
           AND pm.meta_value > pm2.meta_value
       LIMIT 999
   ";
   
   // Get the post IDs.
   $ids = $wpdb->get_col( $query );
   
   // Then use the IDs as the post__in value.
   $foobar_query = new WP_Query([
       'post_type'      => 'foobar',
       'post_status'    => 'publish',
       'posts_per_page' => 999,
       'post__in'       => $ids,
   ]);
   

2. Or use the posts_clauses hook to add the above two JOIN clauses and also the last three conditions in the WHERE clause.

   Example using (a closure and) a custom query arg named _updated_at as a flag indicating whether we should filter the posts query clauses or not, to avoid other WP_Query queries from being affected:

   // Add the filter.
   add_filter( 'posts_clauses', function ( $clauses, $query ) {
       if ( '> build_ran_at' === $query->get( '_updated_at' ) ) {
           global $wpdb;
   
           $pm = uniqid( 'pm_' ); // unique table alias
           $clauses['join'] .= " INNER JOIN $wpdb->postmeta $pm ON {$pm}.post_id = {$wpdb->posts}.ID";
   
           $pm2 = uniqid( 'pm_' ); // unique table alias
           $clauses['join'] .= " INNER JOIN $wpdb->postmeta $pm2 ON {$pm2}.post_id = {$wpdb->posts}.ID";
   
           $clauses['where'] .= " AND ( {$pm}.meta_key = 'updated_at' AND {$pm2}.meta_key = 'build_ran_at'"
               "AND {$pm}.meta_value > {$pm2}.meta_value )";
       }
   
       return $clauses;
   }, 10, 2 );
   
   // Then use the _updated_at arg in place of meta_query.
   $foobar_query = new WP_Query([
       'post_type'      => 'foobar',
       'post_status'    => 'publish',
       'posts_per_page' => 999,
       '_updated_at'    => '> build_ran_at',
   ]);
   

Answer 2

Do not use post meta for this kind of custom data queries. The use of meta value is very slow. You should better use a custom tables and provide custom WP_Query arguments that perfectly fit your needs.