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 );

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 );

• woocommerce-offtopic
• meta-query
• user-meta

1 Answer 1

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 …
}