Filter posts by comparing custom meta value against postdate

Question

I have custom meta fields, delivery_date and collection_date on WooCommerce orders.

I actually have various fields with the same data, in various formats (Y-m-d; U; D d/m/Y) any of which may be useful.

I'd like to list (in an admin page; is_admin() && $query->is_main_query()) all invalid orders - those where the delivery date or collection date is present and is prior to the postdate.*

I can't see how to create meta_querys which compare a postmeta value with a post value, do I need to use a posts_join and posts_where?

Apologies if I haven't explained this well.

---

edit: The following MySQL query fetches the correct posts:

SELECT p.ID, p.post_date, pm.meta_value, pm.meta_key
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID 
WHERE pm.meta_key = '_delivery_date_ymd'
AND pm.meta_value != ''
AND pm.meta_value < p.post_date

---

Similar, but unanswered question: How can I create a WP_Query that returns posts where one meta_value <= another meta_value

---

*- This occurs when WooCommerce Subscriptions create renewal orders and the postmeta is copied verbatim.

Answer 1

I think the below solution will work-

add_action( 'pre_get_posts', 'the_dramatist_filter_shop_order' );
function the_dramatist_filter_shop_order( $query ) {

    if( is_admin() && $query->is_main_query() && in_array( $query->get('post_type'), array('shop_order') ) ) {
        add_filter( 'posts_join', 'the_dramatist_shop_order_query_join' );
        add_filter( 'posts_where', 'the_dramatist_shop_order_filter_date' );
    }
}

function the_dramatist_shop_order_filter_date( $where ){
    global $wpdb;
    $where .= " AND pm.meta_key = '_delivery_date_ymd' AND pm.meta_value != '' AND pm.meta_value < " . $wpdb->prefix ."posts.post_date";
    return $where;
}

function the_dramatist_shop_order_query_join( $join ){
    global $wpdb;
    $join .= ' RIGHT JOIN ' . $wpdb->prefix . 'postmeta pm ON pm.post_id = ' . $wpdb->prefix . 'posts.ID ';
    return $join;
}

// remove_filter('posts_join', 'the_dramatist_shop_order_query_join' ); // remove filter because not to effect another query to use after this query.
// remove_filter('posts_where', 'the_dramatist_shop_order_filter_date' ); // remove filter because not to effect another query to use after this query.

I've not found any way to remove this filters after pre_get_posts query is completed. It's better and best practice to remove those filters after the query is completed. But also it's not that necessary here cause we are only running this query on shop_order post type.

Hope that helps.