Automattic\WooCommerce\Internal\DataStores\Orders

LegacyDataHandler::build_sql_query_for_cleanup │ private │ WC 1.0

Builds a SQL statement to either count or obtain IDs for orders in need of cleanup.

Method of the class: LegacyDataHandler{}

No Hooks.

Returns

String. SQL query.

Usage

// private - for code of main (parent) class only
$result = $this->build_sql_query_for_cleanup( $order_ids, $result, $limit ): string;
$order_ids(array)
If provided, the query will only include orders in this set of order IDs or ID ranges (like "10-100").
Default: array()
$result(string)
Use 'count' to build a query that returns a count. Otherwise, the query will return order IDs.
Default: 'ids'
$limit(int)
If provided, the query will be limited to this number of results. Does not apply when $result is 'count'.

LegacyDataHandler::build_sql_query_for_cleanup() code WC 10.4.3

woocommerce/src/Internal/DataStores/Orders/LegacyDataHandler.php 
private function build_sql_query_for_cleanup( array $order_ids = array(), string $result = 'ids', int $limit = 0 ): string {
	global $wpdb;

	$hpos_orders_table = $this->data_store->get_orders_table_name();

	$sql_where = '';

	if ( $order_ids ) {
		// Expand ranges in $order_ids as needed to build the WHERE clause.
		$where_ids    = array();
		$where_ranges = array();

		foreach ( $order_ids as &$arg ) {
			if ( is_numeric( $arg ) ) {
				$where_ids[] = absint( $arg );
			} elseif ( preg_match( '/^(\d+)-(\d+)$/', $arg, $matches ) ) {
				$where_ranges[] = $wpdb->prepare( "({$wpdb->posts}.ID >= %d AND {$wpdb->posts}.ID <= %d)", absint( $matches[1] ), absint( $matches[2] ) );
			}
		}

		if ( $where_ids ) {
			$where_ranges[] = "{$wpdb->posts}.ID IN (" . implode( ',', $where_ids ) . ')';
		}

		if ( ! $where_ranges ) {
			$sql_where .= '1=0';
		} else {
			$sql_where .= '(' . implode( ' OR ', $where_ranges ) . ')';
		}
	}

	$sql_where .= $sql_where ? ' AND ' : '';

	// Post type handling.
	$sql_where .= '(';
	$sql_where .= "{$wpdb->posts}.post_type IN ('" . implode( "', '", esc_sql( wc_get_order_types( 'cot-migration' ) ) ) . "')";
	$sql_where .= $wpdb->prepare(
		" OR (post_type = %s AND ( {$hpos_orders_table}.id IS NULL OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} WHERE post_id = {$wpdb->posts}.ID)) )", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$this->data_synchronizer::PLACEHOLDER_ORDER_POST_TYPE
	);
	$sql_where .= ')';

	// Exclude 'auto-draft' since those go away on their own.
	$sql_where .= $wpdb->prepare( " AND {$wpdb->posts}.post_status != %s", 'auto-draft' );

	if ( 'count' === $result ) {
		$sql_fields = 'COUNT(*)';
		$sql_limit  = '';
	} else {
		$sql_fields = "{$wpdb->posts}.ID";
		$sql_limit  = $limit > 0 ? $wpdb->prepare( 'LIMIT %d', $limit ) : '';
	}

	$sql = "SELECT {$sql_fields} FROM {$wpdb->posts} LEFT JOIN {$hpos_orders_table} ON {$wpdb->posts}.ID = {$hpos_orders_table}.id WHERE {$sql_where} {$sql_limit}";
	return $sql;
}