Automattic\WooCommerce\Database\Migrations

MigrationHelper::migrate_country_states_for_orders() │ private static │ WC 1.0

Migrate state codes for orders in the orders table and in the posts table. It will migrate only N2(number of states) records, being N equal to 100 by default but this number can be modified via the woocommerce_migrate_country_states_for_orders_batch_size filter.

Method of the class: MigrationHelper{}

Return

true|false. True if there are more records that need to be migrated, false otherwise.

Usage

$result = MigrationHelper::migrate_country_states_for_orders( $country_code, $old_to_new_states_mapping ): bool;

$country_code(string) (required)

The country that has the states for which the migration is needed.

$old_to_new_states_mapping(array) (required)

An associative array where keys are the old state codes and values are the new state codes.

MigrationHelper::migrate_country_states_for_orders() MigrationHelper::migrate country states for orders code ^{WC 8.7.0}

private static function migrate_country_states_for_orders( string $country_code, array $old_to_new_states_mapping ): bool {
	global $wpdb;

	/**
	 * Filters the value of N, where the maximum count of database records that will be updated in one single run of migrate_country_states_for_orders
	 * is N*2*count($old_to_new_states_mapping) if the woocommerce_orders table exists, or N*count($old_to_new_states_mapping) otherwise.
	 *
	 * @param int $batch_size Default value for the count of records to update.
	 * @param string $country_code Country code for the update.
	 * @param array  $old_to_new_states_mapping Associative array of old to new state codes.
	 *
	 * @since 7.2.0
	 */
	$limit      = apply_filters( 'woocommerce_migrate_country_states_for_orders_batch_size', 100, $country_code, $old_to_new_states_mapping );
	$cot_exists = wc_get_container()->get( DataSynchronizer::class )->check_orders_table_exists();

	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

	foreach ( $old_to_new_states_mapping as $old_state => $new_state ) {
		if ( $cot_exists ) {
			$update_query = $wpdb->prepare(
				"UPDATE {$wpdb->prefix}wc_order_addresses SET state=%s WHERE country=%s AND state=%s LIMIT %d",
				$new_state,
				$country_code,
				$old_state,
				$limit
			);

			$wpdb->query( $update_query );
		}

		// We need to split the update query for the postmeta table in two, select + update,
		// because MySQL doesn't support the LIMIT keyword in multi-table UPDATE statements.

		$select_meta_ids_query = $wpdb->prepare(
			"SELECT meta_id FROM {$wpdb->prefix}postmeta,
				(SELECT DISTINCT post_id FROM {$wpdb->prefix}postmeta
				WHERE (meta_key = '_billing_country' OR meta_key='_shipping_country') AND meta_value=%s)
				AS states_in_country
			WHERE (meta_key='_billing_state' OR meta_key='_shipping_state')
			AND meta_value=%s
			AND {$wpdb->postmeta}.post_id = states_in_country.post_id
			LIMIT %d",
			$country_code,
			$old_state,
			$limit
		);

		$meta_ids = $wpdb->get_results( $select_meta_ids_query, ARRAY_A );
		if ( ! empty( $meta_ids ) ) {
			$meta_ids                    = ArrayUtil::select( $meta_ids, 'meta_id' );
			$meta_ids_as_comma_separated = '(' . join( ',', $meta_ids ) . ')';

			$update_query = $wpdb->prepare(
				"UPDATE {$wpdb->prefix}postmeta
				SET meta_value=%s
				WHERE meta_id IN {$meta_ids_as_comma_separated}",
				$new_state
			);

			$wpdb->query( $update_query );
		}
	}

	$states_as_comma_separated = "('" . join( "','", array_keys( $old_to_new_states_mapping ) ) . "')";

	$posts_exist_query = $wpdb->prepare(
		"
		SELECT 1 FROM {$wpdb->prefix}postmeta
		WHERE (meta_key='_billing_state' OR meta_key='_shipping_state')
		AND meta_value IN {$states_as_comma_separated}
		AND post_id IN (
			SELECT post_id FROM {$wpdb->prefix}postmeta WHERE
			(meta_key = '_billing_country' OR meta_key='_shipping_country')
			AND meta_value=%s
		)",
		$country_code
	);

	if ( $cot_exists ) {
		$more_exist_query = $wpdb->prepare(
			"
		SELECT EXISTS(
			SELECT 1 FROM {$wpdb->prefix}wc_order_addresses
			WHERE country=%s AND state IN {$states_as_comma_separated}
		)
		OR EXISTS (
		  {$posts_exist_query}
		)",
			$country_code
		);
	} else {
		$more_exist_query = "SELECT EXISTS ({$posts_exist_query})";
	}

	return (int) ( $wpdb->get_var( $more_exist_query ) ) !== 0;

	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
}