Script to delete specific order notes in WooCommerce

delete-order-notes.php

<?php
/**
 * Removes order notes older than 12 months.
 *
 * There are a lot of automated notes left on orders and subscriptions.
 * After 12 months, these types are notes are no longer needed for debugging purposes.
 *
 * Run:
 * wp eval-file delete-order-notes.php
 */

WP_CLI::log( 'Querying order and subscription notes...' );
set_time_limit( 0 );

/** @var \wpdb $wpdb */
global $wpdb;

// Limits the query to 50,000 records at a time.
$query_limit = 50000;

// Deletes 1,000 records at a time.
$batch_limit = 1000;

$subscription_notes = [
	'Subscription renewal payment due: Status changed from Active to On hold.',
	'Subscription renewal payment retry: Status changed from Renewal Failed to On hold.',
	'Status changed from On hold to Active.',
	'Status changed from On hold to Renewal Failed.',
	'Payment status marked complete.',
	'Subscription On-hold: "Yum Box".',
	'Activated Subscription "Yum Box".',
	'Subscription On-hold: "Yum Yum Box".',
	'Activated Subscription "Yum Yum Box".',
];

$order_notes = [
	'Order status changed from Pending payment to Processing.',
	'Order status changed from Processing to Label Printed.',
	'Order synced to TaxJar',
	'Retry rule applied: Order status changed from Failed to Pending payment.',
	'Retry rule applied: Status changed from On hold to Renewal Failed.',
	'Manual sync to Fulfil was successful.',
];

$notes = array_merge( $subscription_notes, $order_notes );

$notes_string = implode( "','", $notes );

// Will loop until no more results are found.
while ( true ) {
	$results = $wpdb->get_results(
		"SELECT comment_ID FROM {$wpdb->comments}
		WHERE comment_content IN ('{$notes_string}') AND comment_date < DATE_ADD(NOW(), INTERVAL -12 MONTH) LIMIT $query_limit",
		ARRAY_A
	);

	if ( ! $results ) {
		WP_CLI::success( 'No subscription or order notes found to delete.' );
		break;
	}

	WP_CLI::log( 'Found ' . count( $results ) . ' subscription and order notes.' );

	// Let's process the $query_limit results with a batch of $batch_limit records at a time.
	$num_batches = ceil( count( $results ) / $batch_limit );
	$progress    = \WP_CLI\Utils\make_progress_bar( 'Deleting notes', $num_batches );

	for ( $i = 0; $i < $num_batches; $i++ ) {
		$batch = array_slice( $results, $i * $batch_limit, $batch_limit );

		$comment_ids = array_map(
			function( $result ) {
				return $result['comment_ID'];
			},
			$batch
		);

		$comment_ids = implode( ',', $comment_ids );

		$wpdb->query( "DELETE FROM {$wpdb->commentmeta} WHERE comment_id IN ($comment_ids)" );
		$wpdb->query( "DELETE FROM {$wpdb->comments} WHERE comment_ID IN ($comment_ids)" );

		$progress->tick();
	}

	$progress->finish();

	WP_CLI::log( 'Sleeping for 2 seconds.' );
	sleep( 2 );
}