Automattic\WooCommerce\Internal\DataStores\Orders

OrdersTableSearchQuery::generate_where_for_meta_table │ private │ WC 1.0

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.

Method of the class: OrdersTableSearchQuery{}

No Hooks.

Returns

String. The where clause for meta table.

Usage

// private - for code of main (parent) class only
$result = $this->generate_where_for_meta_table(): string;

OrdersTableSearchQuery::generate_where_for_meta_table() code WC 10.8.1

woocommerce/src/Internal/DataStores/Orders/OrdersTableSearchQuery.php 
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
}