Move stranded products to Trash and disassociate with non-existent Product Sets.

trash-stranded-products.php

<?php

/**
 * Automatically delete "Stranded" products.
 *
 * REMOVE CODE AFTER STRANDED PRODUCTS SUCCESSFULLY DELETED!!!
 */
add_action( 'init', function () {

	global $wpdb;

	// Get IDs of all Product Sets which have been permanently deleted.
	$deleted_product_set_ids = $wpdb->get_col( "
		SELECT pm.meta_value
		FROM $wpdb->postmeta as pm 
		WHERE pm.meta_key = '_dfrps_product_set_id' 
		AND pm.meta_value NOT IN (SELECT ID FROM $wpdb->posts WHERE post_type = 'datafeedr-productset')
		GROUP BY pm.meta_value;
	" );

	// If $deleted_product_set_ids is empty, this means we have no products associated with Product Sets which are deleted.
	if ( empty( $deleted_product_set_ids ) ) {
		return;
	}

	// Cast all IDs to integer values.
	$deleted_product_set_ids = array_map( 'absint', $deleted_product_set_ids );

	// Get array of Product IDs which have one of the Deleted Product Set IDs (20 products at a time).
	// These are possibly "stranded" products because they could belong to 1 Product Set which was
	// deleted but also belong to another Product Set which still exists.
	$possibly_stranded_product_ids = get_posts( [
		'fields'         => 'ids',
		'posts_per_page' => 20,
		'post_type'      => 'product',
		'post_status'    => [ 'publish' ],
		'meta_query'     => array(
			array(
				'key'     => '_dfrps_product_set_id',
				'value'   => $deleted_product_set_ids,
				'compare' => 'IN',
			),
		),
	] );

	// Loop through each possible stranded Product.
	foreach ( $possibly_stranded_product_ids as $product_id ) {

		// Get all Product Set IDs ('_dfrps_product_set_id') associated with this product and cast values to integers.
		$product_set_ids = array_map( 'absint', get_post_meta( $product_id, '_dfrps_product_set_id' ) );

		// We use this array to store Product Set IDs which do not exist.
		$deleted_ids = [];

		// Loop through each Product Set ID for this product.
		foreach ( $product_set_ids as $product_set_id ) {

			// Cast Product Set ID to integer.
			$product_set_id = absint( $product_set_id );

			// If this Product Set ID is in the $deleted_product_set_ids array, add its ID to the $deleted_ids array.
			if ( in_array( $product_set_id, $deleted_product_set_ids, true ) ) {
				$deleted_ids[] = $product_set_id;
			}
		}

		// Loop through $deleted_ids array and delete '_dfrps_product_set_id' postmeta value for this $deleted_id.
		foreach ( $deleted_ids as $deleted_id ) {
			delete_post_meta( $product_id, '_dfrps_product_set_id', $deleted_id );
		}

		// If the number of $deleted_ids is equal to the number of Product Set IDs associated with this product
		// this means that there are no Product Sets associated with this product that still exist.
		// So send this product to the Trash!
		if ( count( $deleted_ids ) === count( $product_set_ids ) ) {
			wp_trash_post( $product_id );
		}
	}
} );