How to get coupons from email restrictions with efficiency in WooCommerce

I have the following loop for getting Woocommerce coupons on a page within the my account section of a customers dashboard.

Currently we have 10k+ coupons and just by performing this loop, it's a huge drain on resources and not very efficient causing time outs. Are there any obvious ways in which I can improve the efficiency of it?

Is there a way I can limit the loop to the only search for emails in the "Allowed emails" field (as each coupon is tied to an email address)?

<?php $smart_coupons = get_posts( array(
    'posts_per_page'   => -1,
    'orderby'          => 'name',
    'order'            => 'desc',
    'post_type'        => 'shop_coupon',
    'post_status'      => 'publish'
) );
if ( $smart_coupons ) {
  foreach( $smart_coupons as $smart_coupon) {
    $strcode = strtolower($smart_coupon->post_title);
    $full_coupon = new WC_Coupon( $strcode ); ?>

      <?php if($full_coupon->discount_type == "smart_coupon"){

        $emails = $full_coupon->get_email_restrictions();
        if (in_array($current_email, $emails)) {
          if($full_coupon->usage_count < $full_coupon->usage_limit){ ?>

            coupon content

          <?php }
        }
      }
  }
}

Solution

As email restrictions are in an array (so an indexed array in the database) it is not possible to get that from a meta query in your WP_Query for many technical reasons.

Now instead you can do a custom very light and effective SQL query to get the "smart" coupons that belong to an email, using the WPDB Class.

I have embedded this SQL query in the function below (where the $discount_type argument is already set buy default to "smart_coupon"):

function get_coupons_from_email( $current_email, $discount_type = 'smart_coupon' ) {
    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
        INNER JOIN {$wpdb->prefix}postmeta pm2
            ON p.ID = pm2.post_id
        WHERE p.post_type = 'shop_coupon'
            AND p.post_status = 'publish'
            AND pm.meta_key = 'discount_type'
            AND pm.meta_value = '%s'
            AND pm2.meta_key = 'customer_email'
            AND pm2.meta_value LIKE '%s'
        ORDER BY p.post_name DESC
    ", $discount_type, '%'.$current_email.'%' ) );
}

Code goes in functions.php file of the active child theme (or active theme). Tested and works.

Now you can use it in your code as follows:

// Get smart coupons from email
$smart_coupon_codes = get_coupons_from_email( $current_email );

if ( count($smart_coupon_codes) > 0 ) {
    // Loop through smart coupons code
    foreach ( $smart_coupon_codes as $coupon_code ) {
        $coupon = new WC_Coupon( $coupon_code ); // Get the WC_Coupon Object

        if( $coupon->get_usage_count() < $coupon->get_usage_limit() ){ 
            ?>
            <p>coupon content</p>
            <?php
        }
    }
}

It should work smoothly now.