Na}{

File Content: `OrdersTableSearchQuery.php`

<?php

namespace Automattic\WooCommerce\Internal\DataStores\Orders;

use Automattic\WooCommerce\Internal\Utilities\DatabaseUtil;
use Exception;

/**
 * Creates the join and where clauses needed to perform an order search using Custom Order Tables.
 *
 * @internal
 */
class OrdersTableSearchQuery {
	/**
	 * Holds the Orders Table Query object.
	 *
	 * @var OrdersTableQuery
	 */
	private $query;

	/**
	 * Holds the search term to be used in the WHERE clauses.
	 *
	 * @var string
	 */
	private $search_term;

	/**
	 * Limits the search to a specific field.
	 *
	 * @var string[]
	 */
	private $search_filters;

	/**
	 * Alias used for the derived table that holds FTS product hits.
	 */
	private const PRODUCTS_JOIN_ALIAS = 'fts_items';

	/**
	 * Alias used for the derived table that holds FTS customer/address hits.
	 */
	private const CUSTOMERS_JOIN_ALIAS = 'fts_addresses';

	/**
	 * Creates the JOIN and WHERE clauses needed to execute a search of orders.
	 *
	 * @internal
	 *
	 * @param OrdersTableQuery $query The order query object.
	 */
	public function __construct( OrdersTableQuery $query ) {
		$this->query          = $query;
		$this->search_term    = $query->get( 's' );
		$this->search_filters = $this->sanitize_search_filters( $query->get( 'search_filter' ) ?? '' );
	}

	/**
	 * Sanitize search filter param.
	 *
	 * @param string $search_filter Search filter param.
	 *
	 * @return array Array of search filters.
	 */
	private function sanitize_search_filters( string $search_filter ): array {
		$core_filters = array(
			'order_id',
			'transaction_id',
			'customer_email',
			'customers', // customers also searches in meta.
			'products',
		);

		if ( 'all' === $search_filter || '' === $search_filter ) {
			return $core_filters;
		} else {
			return array( $search_filter );
		}
	}

	/**
	 * Supplies an array of clauses to be used in an order query.
	 *
	 * @internal
	 * @throws Exception If unable to generate either the JOIN or WHERE SQL fragments.
	 *
	 * @return array {
	 *     @type string $join  JOIN clause.
	 *     @type string $where WHERE clause.
	 * }
	 */
	public function get_sql_clauses(): array {
		return array(
			'join'  => array( $this->generate_join() ),
			'where' => array( $this->generate_where() ),
		);
	}

	/**
	 * Generates the necessary JOIN clauses for the order search to be performed.
	 *
	 * @throws Exception May be triggered if a table name cannot be determined.
	 *
	 * @return string
	 */
	private function generate_join(): string {
		$join = array();

		foreach ( $this->search_filters as $search_filter ) {
			$join[] = $this->generate_join_for_search_filter( $search_filter );
		}

		return implode( ' ', $join );
	}

	/**
	 * Generate JOIN clause for a given search filter.
	 * Right now we only have the products filter that actually does a JOIN, but in the future we may add more -- for example, custom order fields, payment tokens, and so on. This function makes it easier to add more filters in the future.
	 *
	 * If a search filter needs a JOIN, it will also need a WHERE clause.
	 *
	 * @param string $search_filter Name of the search filter.
	 *
	 * @return string JOIN clause.
	 */
	private function generate_join_for_search_filter( $search_filter ): string {
		$join = '';

		if ( 'products' === $search_filter ) {
			$join = $this->maybe_get_join_for_products();
		}

		if ( 'customers' === $search_filter ) {
			$join = $this->maybe_get_join_for_customers();
		}
		/**
		 * Filter to support adding a custom order search filter.
		 * Provide a JOIN clause for a new search filter. This should be used along with `woocommerce_hpos_admin_search_filters`
		 * to declare a new custom filter, and `woocommerce_hpos_generate_where_for_search_filter` to generate the WHERE
		 * clause.
		 *
		 * Hardcoded JOINS (products) cannot be modified using this filter for consistency.
		 *
		 * @since 8.9.0
		 *
		 * @param string $join The JOIN clause.
		 * @param string $search_term The search term.
		 * @param string $search_filter The search filter. Use this to bail early if this is not filter you are interested in.
		 * @param OrdersTableQuery $query The order query object.
		 */
		return apply_filters(
			'woocommerce_hpos_generate_join_for_search_filter',
			$join,
			$this->search_term,
			$search_filter,
			$this->query
		);
	}

	/**
	 * Returns a prepared JOIN fragment for products when FTS is enabled.
	 *
	 * @since 10.1.0
	 * @return string JOIN clause or empty string if FTS disabled.
	 */
	private function maybe_get_join_for_products(): string {
		global $wpdb;

		$db_util      = wc_get_container()->get( DatabaseUtil::class );
		$items_table  = $this->query->get_table_name( 'items' );
		$orders_table = $this->query->get_table_name( 'orders' );

		$fts_enabled = get_option( CustomOrdersTableController::HPOS_FTS_INDEX_OPTION ) === 'yes'
			&& get_option( CustomOrdersTableController::HPOS_FTS_ORDER_ITEM_INDEX_CREATED_OPTION ) === 'yes';

		if ( ! $fts_enabled ) {
			return '';
		}

		$search_pattern = $wpdb->esc_like( $db_util->sanitise_boolean_fts_search_term( $this->search_term ) );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
		return $wpdb->prepare(
			"LEFT JOIN (
				SELECT DISTINCT order_id
				FROM $items_table
				WHERE MATCH ( order_item_name ) AGAINST ( %s IN BOOLEAN MODE )
			) AS " . self::PRODUCTS_JOIN_ALIAS . ' ON ' . self::PRODUCTS_JOIN_ALIAS . ".order_id = $orders_table.id",
			$search_pattern
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Returns a prepared JOIN fragment for customers/addresses when FTS is enabled.
	 *
	 * @since 10.1.0
	 * @return string JOIN clause or empty string if FTS disabled.
	 */
	private function maybe_get_join_for_customers(): string {
		global $wpdb;

		$db_util       = wc_get_container()->get( DatabaseUtil::class );
		$address_table = $this->query->get_table_name( 'addresses' );
		$orders_table  = $this->query->get_table_name( 'orders' );

		$fts_enabled = get_option( CustomOrdersTableController::HPOS_FTS_INDEX_OPTION ) === 'yes'
			&& get_option( CustomOrdersTableController::HPOS_FTS_ADDRESS_INDEX_CREATED_OPTION ) === 'yes';

		if ( ! $fts_enabled ) {
			return '';
		}

		$search_pattern = $wpdb->esc_like( $db_util->sanitise_boolean_fts_search_term( $this->search_term ) );

		// Support for phone was added in 9.4.
		$maybe_phone_field = '';
		if ( version_compare( get_option( 'woocommerce_db_version' ), '9.4.0', '>=' ) ) {
			$maybe_phone_field = ', phone';
		}

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
		return $wpdb->prepare(
			"LEFT JOIN (
				SELECT DISTINCT order_id
				FROM $address_table
				WHERE MATCH (
					first_name, last_name, company,
					address_1,  address_2, city,  state,
					postcode,   country,   email  $maybe_phone_field
				) AGAINST ( %s IN BOOLEAN MODE )
			) AS " . self::CUSTOMERS_JOIN_ALIAS . ' ON ' . self::CUSTOMERS_JOIN_ALIAS . ".order_id = $orders_table.id",
			$search_pattern
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Generates the necessary WHERE clauses for the order search to be performed.
	 *
	 * @throws Exception May be triggered if a table name cannot be determined.
	 *
	 * @return string
	 */
	private function generate_where(): string {
		$where             = array();
		$possible_order_id = (string) absint( $this->search_term );
		$order_table       = $this->query->get_table_name( 'orders' );

		// Support the passing of an order ID as the search term.
		if ( (string) $this->query->get( 's' ) === $possible_order_id ) {
			$where[] = "`$order_table`.id = $possible_order_id";
		}

		foreach ( $this->search_filters as $search_filter ) {
			$search_where = trim( $this->generate_where_for_search_filter( $search_filter ) );
			if ( strlen( $search_where ) > 0 ) {
				$where[] = $search_where;
			}
		}

		$where_statement = implode( ' OR ', $where );

		return ( strlen( $where_statement ) > 0 ) ? " ( $where_statement ) " : '';
	}

	/**
	 * Generates WHERE clause for a given search filter. Right now we only have the products and customers filters that actually use WHERE, but in the future we may add more -- for example, custom order fields, payment tokens and so on. This function makes it easier to add more filters in the future.
	 *
	 * @param string $search_filter Name of the search filter.
	 *
	 * @return string WHERE clause.
	 */
	private function generate_where_for_search_filter( string $search_filter ): string {
		global $wpdb;

		$order_table = $this->query->get_table_name( 'orders' );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $order_table is hardcoded.
		if ( 'customer_email' === $search_filter ) {
			return $wpdb->prepare(
				"`$order_table`.billing_email LIKE %s",
				$wpdb->esc_like( $this->search_term ) . '%'
			);
		}

		if ( 'order_id' === $search_filter && is_numeric( $this->search_term ) ) {
			return $wpdb->prepare(
				"`$order_table`.id = %d",
				absint( $this->search_term )
			);
		}

		if ( 'transaction_id' === $search_filter ) {
			return $wpdb->prepare(
				"`$order_table`.transaction_id LIKE %s",
				'%' . $wpdb->esc_like( $this->search_term ) . '%'
			);
		}
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( 'products' === $search_filter ) {
			return $this->get_where_for_products();
		}

		if ( 'customers' === $search_filter ) {
			return $this->get_where_for_customers();
		}

		/**
		 * Filter to support adding a custom order search filter.
		 * Provide a WHERE clause for a custom search filter via this filter. This should be used with the
		 * `woocommerce_hpos_admin_search_filters` to declare a new custom filter, and optionally also with the
		 * `woocommerce_hpos_generate_join_for_search_filter` filter if a join is also needed.
		 *
		 * Hardcoded filters (products, customers, ID and email) cannot be modified using this filter for consistency.
		 *
		 * @since 8.9.0
		 *
		 * @param string $where WHERE clause to add to the search query.
		 * @param string $search_term The search term.
		 * @param string $search_filter Name of the search filter. Use this to bail early if this is not the filter you are looking for.
		 * @param OrdersTableQuery $query The order query object.
		 */
		return apply_filters(
			'woocommerce_hpos_generate_where_for_search_filter',
			'',
			$this->search_term,
			$search_filter,
			$this->query
		);
	}

	/**
	 * Helper function to generate the WHERE clause for products search. Uses FTS when available.
	 *
	 * @return string|null WHERE clause for products search.
	 */
	private function get_where_for_products() {
		global $wpdb;
		$db_util      = wc_get_container()->get( DatabaseUtil::class );
		$items_table  = $this->query->get_table_name( 'items' );
		$orders_table = $this->query->get_table_name( 'orders' );
		$fts_enabled  = get_option( CustomOrdersTableController::HPOS_FTS_INDEX_OPTION ) === 'yes' && get_option( CustomOrdersTableController::HPOS_FTS_ORDER_ITEM_INDEX_CREATED_OPTION ) === 'yes';

		if ( $fts_enabled ) {
			return self::PRODUCTS_JOIN_ALIAS . '.order_id IS NOT NULL';
		}

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $orders_table and $items_table are hardcoded.
		return $wpdb->prepare(
			"
$orders_table.id in (
	SELECT order_id FROM $items_table search_query_items WHERE
	search_query_items.order_item_name LIKE %s
)
",
			'%' . $wpdb->esc_like( $this->search_term ) . '%'
		);
		// phpcs:enable
	}

	/**
	 * Helper function to generate the WHERE clause for customers search. Uses FTS when available.
	 *
	 * @return string|null WHERE clause for customers search.
	 */
	private function get_where_for_customers() {
		global $wpdb;
		$order_table   = $this->query->get_table_name( 'orders' );
		$address_table = $this->query->get_table_name( 'addresses' );

		$db_util = wc_get_container()->get( DatabaseUtil::class );

		$fts_enabled = get_option( CustomOrdersTableController::HPOS_FTS_INDEX_OPTION ) === 'yes' && get_option( CustomOrdersTableController::HPOS_FTS_ADDRESS_INDEX_CREATED_OPTION ) === 'yes';

		if ( $fts_enabled ) {
			return self::CUSTOMERS_JOIN_ALIAS . '.order_id IS NOT NULL';
		}

		$meta_sub_query = $this->generate_where_for_meta_table();
		return "`$order_table`.id IN ( $meta_sub_query ) ";
	}

	/**
	 * Generates where clause for meta table.
	 *
	 * Note we generate the where clause as a subquery to be used by calling function inside the IN clause. This is against the general wisdom for performance, but in this particular case, a subquery is able to use the order_id-meta_key-meta_value index, which is not possible with a join.
	 *
	 * Since it can use the index, which otherwise would not be possible, it is much faster than both LEFT JOIN or SQL_CALC approach that could have been used.
	 *
	 * @return string The where clause for meta table.
	 */
	private function generate_where_for_meta_table(): string {
		global $wpdb;
		$meta_table  = $this->query->get_table_name( 'meta' );
		$meta_fields = $this->get_meta_fields_to_be_searched();

		if ( '' === $meta_fields ) {
			return '-1';
		}

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $meta_fields is already escaped before imploding, $meta_table is hardcoded.
		return $wpdb->prepare(
			"
SELECT search_query_meta.order_id
FROM $meta_table as search_query_meta
WHERE search_query_meta.meta_key IN ( $meta_fields )
AND search_query_meta.meta_value LIKE %s
GROUP BY search_query_meta.order_id
",
			'%' . $wpdb->esc_like( $this->search_term ) . '%'
		);
		// phpcs:enable
	}

	/**
	 * Returns the order meta field keys to be searched.
	 *
	 * These will be returned as a single string, where the meta keys have been escaped, quoted and are
	 * comma-separated (ie, "'abc', 'foo'" - ready for inclusion in a SQL IN() clause).
	 *
	 * @return string
	 */
	private function get_meta_fields_to_be_searched(): string {
		$meta_fields_to_search = array(
			'_billing_address_index',
			'_shipping_address_index',
		);

		/**
		 * Controls the order meta keys to be included in search queries.
		 *
		 * This hook is used when Custom Order Tables are in use: the corresponding hook when CPT-orders are in use
		 * is 'woocommerce_shop_order_search_fields'.
		 *
		 * @since 7.0.0
		 *
		 * @param array
		 */
		$meta_keys = apply_filters(
			'woocommerce_order_table_search_query_meta_keys',
			$meta_fields_to_search
		);

		$meta_keys = (array) array_map(
			function ( string $meta_key ): string {
				return "'" . esc_sql( wc_clean( $meta_key ) ) . "'";
			},
			$meta_keys
		);

		return implode( ',', $meta_keys );
	}
}
Name	Size	Permission
CustomOrdersTableController.php	29796 bytes	0644
DataSynchronizer.php	36796 bytes	0644
LegacyDataCleanup.php	7625 bytes	0644
LegacyDataHandler.php	21156 bytes	0644
OrdersTableDataStore.php	110685 bytes	0644
OrdersTableDataStoreMeta.php	5780 bytes	0644
OrdersTableFieldQuery.php	8711 bytes	0644
OrdersTableMetaQuery.php	19136 bytes	0644
OrdersTableQuery.php	46467 bytes	0644
OrdersTableRefundDataStore.php	6163 bytes	0644
OrdersTableSearchQuery.php	14829 bytes	0644
File Content: OrdersTableSearchQuery.php

File Content: `OrdersTableSearchQuery.php`
