2

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() ):
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',
    ]);
    
0

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.

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.