0

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.

  • Do you need this for all post types or for only shop_order post type ? – CodeMascot Dec 22 '16 at 9:28
  • only shop_order (and only when I've checked a checkbox I've added to the filters) – jezmck Dec 22 '16 at 9:31
  • Checkbox ??? I've understood this checkbox part. And you've not mentioned it in your question. – CodeMascot Dec 22 '16 at 9:32
  • The checkbox part isn't really relevant, I only mention it now so that people know that this isn't a permanent filtering. – jezmck Dec 22 '16 at 9:33
2

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.

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.