Fix inaccurate comment counts across a WP multisite via wp cli

gistfile1.txt

/**
 * Fix inaccurate comment counts across a WP multisite
 *
 */

class Multisite_Comments_Fix {

	/**
	 * Class construction.
	 */
	public function __construct() {
		add_action( 'cli_init', [ $this, 'cli' ] );
	}

	/**
	 * Adds WP CLI command
	 *
	 * @return void
	 */
	public function cli() {
		if ( defined( 'WP_CLI' ) && class_exists( 'WP_CLI' ) ) {
			WP_CLI::add_command( 'comment_count_fix', [ $this, 'comment_count' ] );
		}
	}
	
	/**
	 * Fix inaccurate comment counts on all posts across a WP multisite
	 *
	 * @return void
	 */
	public function comment_count() {
		global $wpdb;
		$blog_ids = $wpdb->get_results(
			"SELECT blog_id FROM {$wpdb->base_prefix}blogs", ARRAY_A
		);

		foreach ( $blog_ids as $blog ) :

			$sql = $wpdb->prepare(
				"SELECT ID FROM {$wpdb->base_prefix}%d_posts WHERE post_type IN ('post', 'page')", $blog['blog_id']
			);
			$posts = $wpdb->get_results( $sql );

			foreach ( $posts as $post ) :

				$sql = $wpdb->prepare(
					"SELECT count(*) FROM {$wpdb->base_prefix}%d_comments WHERE comment_post_ID = %d AND comment_approved = 1", $blog['blog_id'], $post->ID
				);
				$comment_count = $wpdb->get_var( $sql );

				if ( $comment_count > 0 ) :
					$table     = $wpdb->prefix . $blog['blog_id'] . '_posts';
					$post_data = [
						'comment_count' => $comment_count
					];
					$update = $wpdb->update( $table, $post_data, [ 'ID' => $post->ID ] );

					// error logging
					if ( false === $update ) :
						$log = "ERROR: Comment count not updated" . ":\n \n" . $post->ID . "\n";
					else :
						$log = "PASS: Comment count updated for post " . $post->ID . " in blog " . $blog['blog_id'] . ": $comment_count \n";
					endif;

					if ( ! empty( $log ) ) :
						error_log( $log );
						WP_CLI::line( $log );
					endif;
				endif;
			endforeach;
		endforeach;
	}
}