Move unused coupons to trash, then empty coupons trash in Woocommerce manually using WP-CLI

delete_coupons.sh

#!/bin/bash

# Sometimes when the trash bin is too large for coupons (or other post_types),
# simply emptying the trash directly in the Wordpress front-end throws 500 errors
# or timeouts, that's why I created this script. Adjust the 'post_type',
# 'delete_batch_type' and 'pause_duration' accordingly.

# Configurable batch size for deleting posts
delete_batch_size=1000

# Configurable pause between batches (in seconds)
pause_duration=2

echo "Getting the list of post IDs..."
post_ids=$(wp post list --post_type=shop_coupon --post_status=trash --format=ids)
echo "Post IDs retrieved."

echo "Calculating the total number of posts..."
total_posts=$(echo "$post_ids" | wc -w)
echo "Total posts: $total_posts"

echo "Splitting the post IDs into batches..."
batches=()
while read -r batch; do
    batches+=("$batch")
done < <(printf "%s\n" "$post_ids" | xargs -n "$delete_batch_size")
echo "Batches created."

echo "Starting deletion process..."
batch_count=0
processed_posts=0
for batch in "${batches[@]}"; do
    batch_count=$((batch_count+1))
    post_ids_batch="$batch"
    num_posts_in_batch=$(echo "$post_ids_batch" | wc -w)
    echo "Processing batch $batch_count of ${#batches[@]} (IDs $processed_posts - $((processed_posts+num_posts_in_batch-1)) of $total_posts)"
    wp post delete $post_ids_batch --force --defer-term-counting
    processed_posts=$((processed_posts+num_posts_in_batch))
    sleep "$pause_duration"
done

echo "Finished deleting $total_posts posts."

move_unused_coupons_to_trash.sql

-- Move unused coupons to the trash, we won't delete them directly,
-- we'll let Wordpress handle that with WP-CLI for safety.
-- Sometimes the query will timeout. If that happens wait some minutes
-- or even an hour and check in the front-end if it worked. If it did
-- not just run the query again. Test and repeat if necessary.
UPDATE wp_posts AS a
LEFT JOIN wp_postmeta AS b ON a.ID = b.post_id AND b.meta_key = 'usage_count'
SET a.post_status = 'trash'
WHERE a.post_type = 'shop_coupon'
AND (b.meta_value IS NULL OR b.meta_value = '0')

usage.sh

# cd your public wordpress installation folder
chmod +x ./delete_coupons.sh
./delete_coupons.sh
# make sure to remove the script from the public folder
rm ./delete_coupons.sh