1

I want to list orders from my Woocommerce shop. I have user meta for my users. (unsubscribed) I only want to list the order if that order's user is unsubscribed === 0.

I have working code to the customers:

$args = array(
    'role'       => 'customer',
    'number'     => - 1,
    'meta_query' => [
        'relation' => 'OR',
        [
            'key'     => 'unsubscribed',
            'compare' => '!=',
            'value'   => 1
        ],
        [
            'key'     => 'unsubscribed',
            'value'   => 0,
            'compare' => 'NOT EXISTS'
        ]
    ],
);

$query  = new WP_User_Query( $args );

I would like to do similar query with wc_get_orders but it doesn't work:

$args   = [
    'limit' => - 1,
    'meta_query' => [
        'relation' => 'OR',
        [
            'key'     => 'unsubscribed',
            'compare' => '!=',
            'value'   => 1
        ],
        [
            'key'     => 'unsubscribed',
            'value'   => 0,
            'compare' => 'NOT EXISTS'
        ]
    ]
];

$orders = wc_get_orders( $args );
0

I see 2 possible ways:

1) Using your WP_User_Query in a WC_Order_Query with the customer_id argument this way:

// Users query
$user_ids = (array) get_users([
    'role'       => 'customer',
    'number'     => - 1,
    'fields'     => 'ID',
    'meta_query' => [
        'relation' => 'OR',
        [
            'key'     => 'unsubscribed',
            'compare' => '!=',
            'value'   => 1
        ],
        [
            'key'     => 'unsubscribed',
            'compare' => 'NOT EXISTS'
        ]
    ],
]);

// Orders query (using the users IDs from the user query)
$orders = wc_get_orders([
    'limit'       => - 1,
    'status'      => ['on-hold','processing','completed'],
    'customer_id' => $user_ids,
]);

// Loop through Order IDs
foreach( $orders as $order ) {
    // Get the Order ID
    $order_id = $order->get_id();

    // And so on …
}

2) Or you can use a unique lighter SQL Query with the WPDB Class like:

$global $wpdb;

$order_ids = $wpdb->get_col( "
    SELECT DISTINCT ID
    FROM {$wpdb->prefix}posts o
    INNER JOIN {$wpdb->prefix}postmeta om
        ON o.ID = om.post_id
    INNER JOIN {$wpdb->prefix}usermeta um
        ON om.meta_value = um.user_id
    INNER JOIN {$wpdb->prefix}usermeta um2
        ON um.user_id = um2.user_id
    WHERE o.post_type = 'shop_order'
    AND o.post_status IN ('wc-on-hold','wc-processing','wc-completed')
    AND om.meta_key = '_customer_user'
    AND um.meta_key = 'wp_capabilities'
    AND um.meta_value LIKE '%customer%'
    AND um2.meta_key = 'unsubscribed'
    AND ( um2.meta_value = '0' OR um2.meta_value = NULL )
");

// Loop through Order IDs
foreach( $order_ids as $order_id ) {
    // Get an instance of the WC_Order Object
    $order = wc_get_order($order_id);

    // And so on …
}
  • Thank you, i used the first solution. – landorid May 14 at 10:03

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.