devinsays/delete-completed-order-meta.php

## delete-completed-order-meta.php
<?php
/**
 * Delete postmeta for completed orders created more than X days ago.
 *
 * To delete meta on completed orders run:
 * wp eval-file delete-completed-order-meta.php
 */

// Transient allows the script to resume where it left off if interrupted.
$transient_name = 'last_processed_order';

// Which order to start on if no transient is set.
$start = 0;

// How many orders to process at a time.
$limit = 1000;

// Only delete meta from orders older than this many days.
$days = 90;

// Meta fields to delete.
// Format: meta_key => meta_value
// If meta_value is "null", the meta field will be deleted regardless of its value.
// If meta_value is a string, the meta field will only be deleted if its value matches.
// If meta_value is '', the meta field will only be deleted if its value is blank.
$meta_to_delete = [
	'_order_stock_reduced' => null,
	'_billing_phone'       => null,
	'is_vat_exempt'        => 'no',
	'_customer_ip_address' => null,
	'_customer_user_agent' => null,
	'_billing_company'     => '',
	'_shipping_company'    => '',
];

// Output script information.
WP_CLI::log( '' );
WP_CLI::log( "Starting post meta deletion for completed orders older than ${days} days." );

// Formats meta for CLI output.
$meta_table = [];
foreach ( $meta_to_delete as $key => $value ) {
	if ( null === $value ) {
		$value = 'null';
	}
	if ( '' === $value ) {
		$value = 'blank';
	}
	$meta_table[] = [
		'key'   => $key,
		'value' => $value,
	];
}
WP_CLI\Utils\format_items( 'table', $meta_table, [ 'key', 'value' ] );
WP_CLI::log( '' );

// Pre-build the SQL query for these meta searches.
$fields = [];
foreach ( $meta_to_delete as $field_name => $value ) {
	if ( null === $value ) {
		$fields[] = "(meta_key = '$field_name')";
	} else {
		$fields[] = "(meta_key = '$field_name' AND meta_value = '$value')";
	}
}

// Need access to the database.
global $wpdb;

while ( true ) {
	// Last ID processed.
	$last_order_id = get_transient( $transient_name ) ? get_transient( $transient_name ) : $start;

	WP_CLI::log( "Querying orders from ID: $last_order_id." );

	// Queries for orders to process.
	// Orders are processed in batches of $limit.
	$results = $wpdb->get_results(
		$wpdb->prepare(
			"SELECT DISTINCT ID AS post_id
			FROM {$wpdb->prefix}posts
			WHERE post_type = 'shop_order'
				AND post_status = 'wc-completed'
				AND ID > %d
				AND post_date < DATE_ADD(NOW(), INTERVAL - %d DAY)
			ORDER BY ID LIMIT %d",
			$last_order_id,
			$days,
			$limit,
		),
		ARRAY_A
	);

	// Stop if DB error.
	if ( $wpdb->last_error ) {
		WP_CLI::error( "DB error. Last order ID: $last_order_id." );
		break;
	}

	// Stop when complete.
	if ( ! $results ) {
		WP_CLI::success( 'Finished! No more orders found.' );
		break;
	}

	// Delete post meta.
	$count_deleted = 0;
	foreach ( $results as $result ) {
		$sql = "DELETE FROM {$wpdb->prefix}postmeta WHERE post_id = %d AND (" . implode( ' OR ', $fields ) . ')';

		$count_deleted += $wpdb->query(
			$wpdb->prepare( $sql, $result['post_id'] )
		);
	}

	$order_count = count( $results );
	WP_CLI::success( "Completed updates for $order_count orders. $count_deleted meta records deleted." );

	// Set new transient.
	$last_order_processed = $results[ $order_count - 1 ]['post_id'];
	set_transient( $transient_name, $last_order_processed, DAY_IN_SECONDS * 90 );

	// Sleeps for two seconds before next query.
	WP_CLI::log( 'Sleeping for 2 seconds.' );
	WP_CLI::log( '' );
	sleep( 2 );
}