Loop with WooCommerce user coupon not working

Question

I'm looping through WooCommerce coupons that are restricted to the logged in customer. The problem is that coupons saved with customer restriction have the post_meta "customer_email" sometimes with single values, sometimes with an array. When making a query with WP_QUERY I can't get the coupons that are with the target "customer_email" in array format. Example of my code:

              // LOOP ACROSS ALL COUNPONS IN WOOCOMMERCE
              $args = array(
                    'post_type' => 'shop_coupon',
                    'meta_query'  => array(
                             'relation' => 'OR',
                             array(
                                 'key'   => 'customer_email',
                                 'value' => array($user_email),
                                 'compare' => 'IN'
                             ),
                             array(
                                 'key'   => 'customer_email',
                                 'value' => $user_email
                             )
                         )

                );

The code above only returns the coupons that were saved with the customer's email uniquely, the coupons that the same email is in an array are not returned. If someone wonders why the email is saved in the customer_email meta, sometimes as unique and sometimes as an array, it happens because if the coupon is generated as only one email allowed, the value is unique, if it is created with more of an email it is saved as an Array. Any idea why my query doesn't return all the coupons that contain the customer's email?

Answer 1

Could you try this :

// LOOP ACROSS ALL COUNPONS IN WOOCOMMERCE
$args = array(
    'post_type' => 'shop_coupon',
    'meta_query'  => array(
        array(
            'key'   => 'customer_email',
            'value' => $user_email,
            'compare' => 'LIKE' // search will match in both cases : single value and array value
        )
    )
);

EDIT add explanation why IN doesn't match in this case 'customer_email' is a string containing all emails separated by comma.

customer_email.value = email1,email2,email3

When we use IN, we are looking for exact corresponding value of DB value and each value we pass in array.

[
    // ...
    "IN" => [ 'email1', 'email2' ]
    // Corresponding search :  customer_email.value = 'email1' OR customer_email.value = 'email2'
    // This can't match because customer_email.value = 'email1,email2,email3'

]

EDIT *** Other way to do

You should have a look to this thread : How to get coupons from email restrictions with efficiency in WooCommerce

Inspired from the above thread :

function get_coupons_names_from_email( $current_email ) {
    global $wpdb;

    return $wpdb->get_col( $wpdb->prepare("
        SELECT p.post_name
        FROM {$wpdb->prefix}posts p
        INNER JOIN {$wpdb->prefix}postmeta pm
            ON p.ID = pm.post_id
        WHERE p.post_type = 'shop_coupon'
            AND p.post_status = 'publish'
            AND pm.meta_key = 'customer_email'
            AND pm.meta_value LIKE '%s'
        ORDER BY p.post_name DESC", 
        '%'.$current_email.'%' ) 
    );
}

This function return all coupons codes restricted to the '$current_email' (customer email).

In your script, if you need WC_Coupon object, you can retrieve it like this :

$user = wp_get_current_user();
$coupons_codes = get_coupons_names_from_email( $user->user_email );
foreach ( $coupons_codes as $coupon_code ) {
    $coupon = new WC_Coupon( $coupon_code ); // Return the WC_Coupon object refreshed with data related to $coupon_code
    // do your stuff
    // $coupon->get_code();
    // $coupon->get_description();
}