questions

WC_Order_Query where meta data does not exist or is not equal to

Rate this post

I am trying to query all orders from the database where a custom post meta field is not set, or is not equal to ‘yes’. It seems I am not able to get it to work properly.

I have this:

$query = new \WC_Order_Query([
    'date_completed' => $range,
    'x_days_follow_up_email' => 'yes' // Note: Compares '!='
]);

Then I have this hooked into woocommerce_order_data_store_cpt_get_orders_query:

if (!empty($query_vars['x_days_follow_up_email'])) {
    $query['meta_query'][] = [
            'key' => 'x_days_follow_up_email',
            'value' => esc_attr($query_vars['x_days_follow_up_email']),
            'compare' => '!='
        ]
    ];
}

return $query;

This returns nothing. If I change the compare to ==, it returns the orders with the meta set to ‘yes’. So I was thinking I might have to include NOT EXISTS somehow, like this:

if (!empty($query_vars['x_days_follow_up_email'])) {
    $query['meta_query'][] = [
        'relation' => 'OR',
        [
            'key' => 'x_days_follow_up_email',
            'value' => esc_attr($query_vars['x_days_follow_up_email']),
            'compare' => '!='
        ],
        [
            'key' => 'x_days_follow_up_email',
            'compare' => 'NOT EXISTS'
        ]

    ];
}

return $query;

This sadly does not work either. I could write my own WP_Query for this, but there are a lot of orders and I find that the query takes ages. The $range in my code is basically all orders that are 11 – 8 days old. And I only want the orders that do not have the meta x_days_follow_up_email, or when that meta is set but not to yes.

It seems that it should be possible? For what it’s worth, this was working before, but it suddenly stopped working after an update for reasons unknown:

$query = new \WC_Order_Query([
    'date_completed' => $range,
    'meta_key' => 'x_days_follow_up_email',
    'meta_value' => 'yes',
    'meta_compare' => '!='
]);

Seems simple enough, but I can’t seem to make it happen.

UPDATE:

I have also tried coding this into a normal WP_Query, but it reaches the maximum execution time of the server and seems very inefficient:

$args = array(
        'post_type' => 'shop_order',
        'post_status' => 'wc-completed',
        'posts_per_page' => -1,
        'meta_query' => array(
            'relation' => 'AND',
            array(
                'key' => '_date_completed',
                'value' => array($rangeStart, $rangeEnd),
                'compare' => 'BETWEEN',
                'type' => 'NUMBER'
            ),
            array(
                'relation' => 'OR',
                array(
                    'key' => 'x_days_follow_up_email',
                    'value' => 'yes',
                    'compare' => '!=',
                ),
                array(
                    'key' => 'x_days_follow_up_email',
                    'compare' => 'NOT EXISTS',
                )
            )
        )
    );

 

✔️Solution:

You need some additional code, to handle custom meta data like:

add_filter( 'woocommerce_order_data_store_cpt_get_orders_query', 'order_data_store_custom_query_var', 10, 2 );
function order_data_store_custom_query_var( $query, $query_vars ) {
    if ( ! empty( $query_vars['xdays_fup'] ) ) {
        $query['meta_query'][] = array(
            'relation' => 'OR',
            array( 
                'key'     => 'x_days_follow_up_email',
                'value'   => esc_attr( $query_vars['xdays_fup'] ),
                'compare' => '!=',
            ),
            array( 
                'key'     => 'x_days_follow_up_email',
                'compare' => 'NOT EXISTS',
            )
        );
    }
    return $query;
}

Then you will change your query to:

$query = new \WC_Order_Query([
    'date_completed' => $range,
    'xdays_fup' => 'yes'
]);

It should work.

Leave a Reply

Your email address will not be published.

Back to top button