OrdersTableQuery.php — WooCommerce 10.9.4

woocommerce / src / Internal / DataStores / Orders / OrdersTableQuery.php

woocommerce / src / Internal / DataStores / Orders Last commit date

CustomOrdersTableController.php 3 months ago DataSynchronizer.php 7 months ago LegacyDataCleanup.php 1 year ago LegacyDataHandler.php 5 months ago OrdersTableDataStore.php 1 month ago OrdersTableDataStoreMeta.php 1 year ago OrdersTableFieldQuery.php 2 years ago OrdersTableMetaQuery.php 1 year ago OrdersTableQuery.php 1 month ago OrdersTableRefundDataStore.php 1 month ago OrdersTableSearchQuery.php 11 months ago

OrdersTableQuery.php

1570 lines

1	<?php
2	// phpcs:disable Generic.Commenting.Todo.TaskFound
3	/**
4	* OrdersTableQuery class file.
5	*/
6
7	namespace Automattic\WooCommerce\Internal\DataStores\Orders;
8
9	use Automattic\WooCommerce\Internal\Utilities\DatabaseUtil;
10
11	defined( 'ABSPATH' ) \|\| exit;
12
13	/**
14	* This class provides a `WP_Query`-like interface to custom order tables.
15	*
16	* @property-read int $found_orders Number of found orders.
17	* @property-read int $found_posts Alias of the `$found_orders` property.
18	* @property-read int $max_num_pages Max number of pages matching the current query.
19	* @property-read array $orders Order objects, or order IDs.
20	* @property-read array $posts Alias of the $orders property.
21	*/
22	class OrdersTableQuery {
23
24	/**
25	* Values to ignore when parsing query arguments.
26	*/
27	public const SKIPPED_VALUES = array( '', array(), null );
28
29	/**
30	* Regex used to catch "shorthand" comparisons in date-related query args.
31	*/
32	public const REGEX_SHORTHAND_DATES = '/([^.<>]*)(>=\|<=\|>\|<\|\.\.\.)([^.<>]+)/';
33
34	/**
35	* Highest possible unsigned bigint value (unsigned bigints being the type of the `id` column).
36	*
37	* This is deliberately held as a string, rather than a numeric type, for inclusion within queries.
38	*/
39	private const MYSQL_MAX_UNSIGNED_BIGINT = '18446744073709551615';
40
41	/**
42	* Names of all COT tables (orders, addresses, operational_data, meta) in the form 'table_id' => 'table name'.
43	*
44	* @var array
45	*/
46	private $tables = array();
47
48	/**
49	* Column mappings for all COT tables.
50	*
51	* @var array
52	*/
53	private $mappings = array();
54
55	/**
56	* Query vars after processing and sanitization.
57	*
58	* @var array
59	*/
60	private $args = array();
61
62	/**
63	* Original query vars used to build this query.
64	*
65	* @var array
66	*/
67	private $query_args = array();
68
69	/**
70	* Columns to be selected in the SELECT clause.
71	*
72	* @var array
73	*/
74	private $fields = array();
75
76	/**
77	* Array of table aliases and conditions used to compute the JOIN clause of the query.
78	*
79	* @var array
80	*/
81	private $join = array();
82
83	/**
84	* Array of fields and conditions used to compute the WHERE clause of the query.
85	*
86	* @var array
87	*/
88	private $where = array();
89
90	/**
91	* Field to be used in the GROUP BY clause of the query.
92	*
93	* @var array
94	*/
95	private $groupby = array();
96
97	/**
98	* Array of fields used to compute the ORDER BY clause of the query.
99	*
100	* @var array
101	*/
102	private $orderby = array();
103
104	/**
105	* Limits used to compute the LIMIT clause of the query.
106	*
107	* @var array
108	*/
109	private $limits = array();
110
111	/**
112	* Results (order IDs) for the current query.
113	*
114	* @var array
115	*/
116	private $orders = array();
117
118	/**
119	* Final SQL query to run after processing of args.
120	*
121	* @var string
122	*/
123	private $sql = '';
124
125	/**
126	* Final SQL query to count results after processing of args.
127	*
128	* @var string
129	*/
130	private $count_sql = '';
131
132	/**
133	* The number of pages (when pagination is enabled).
134	*
135	* @var int
136	*/
137	private $max_num_pages = 0;
138
139	/**
140	* The number of orders found.
141	*
142	* @var int
143	*/
144	private $found_orders = 0;
145
146	/**
147	* Field query parser.
148	*
149	* @var OrdersTableFieldQuery
150	*/
151	private $field_query = null;
152
153	/**
154	* Meta query parser.
155	*
156	* @var OrdersTableMetaQuery
157	*/
158	private $meta_query = null;
159
160	/**
161	* Search query parser.
162	*
163	* @var OrdersTableSearchQuery?
164	*/
165	private $search_query = null;
166
167	/**
168	* Date query parser.
169	*
170	* @var WP_Date_Query
171	*/
172	private $date_query = null;
173
174	/**
175	* Instance of the OrdersTableDataStore class.
176	*
177	* @var OrdersTableDataStore
178	*/
179	private $order_datastore = null;
180
181	/**
182	* Whether to run filters to modify the query or not.
183	*
184	* @var boolean
185	*/
186	private $suppress_filters = false;
187
188	/**
189	* Sets up and runs the query after processing arguments.
190	*
191	* @param array $args Array of query vars.
192	*/
193	public function __construct( $args = array() ) {
194	// Note that ideally we would inject this dependency via constructor, but that's not possible since this class needs to be backward compatible with WC_Order_Query class.
195	$this->order_datastore = wc_get_container()->get( OrdersTableDataStore::class );
196
197	$this->tables = $this->order_datastore::get_all_table_names_with_id();
198	$this->mappings = $this->order_datastore->get_all_order_column_mappings();
199
200	$this->suppress_filters = array_key_exists( 'suppress_filters', $args ) ? (bool) $args['suppress_filters'] : false;
201	unset( $args['suppress_filters'] );
202
203	$this->args = $args;
204	$this->query_args = $args; // Keep a copy of the original vars used to initialize the query.
205
206	// TODO: 'name' arg (post_name equivalent) is not yet implemented for HPOS.
207	unset( $this->args['name'] );
208
209	$this->build_query();
210	if ( ! $this->maybe_override_query() ) {
211	$this->run_query();
212	}
213	}
214
215	/**
216	* Lets the `woocommerce_hpos_pre_query` filter override the query.
217	*
218	* @return boolean Whether the query was overridden or not.
219	*/
220	private function maybe_override_query(): bool {
221	/**
222	* Filters the orders array before the query takes place.
223	*
224	* Return a non-null value to bypass the HPOS default order queries.
225	*
226	* If the query includes limits via the `limit`, `page`, or `offset` arguments, we
227	* encourage the `found_orders` and `max_num_pages` properties to also be set.
228	*
229	* @since 8.2.0
230	*
231	* @param array\|null $order_data {
232	* An array of order data.
233	* @type int[] $orders Return an array of order IDs data to short-circuit the HPOS query,
234	* or null to allow HPOS to run its normal query.
235	* @type int $found_orders The number of orders found.
236	* @type int $max_num_pages The number of pages.
237	* }
238	* @param OrdersTableQuery $query The OrdersTableQuery instance.
239	* @param string $sql Fully built SQL query.
240	*/
241	$pre_query = apply_filters( 'woocommerce_hpos_pre_query', null, $this, $this->sql );
242	if ( ! $pre_query \|\| ! isset( $pre_query[0] ) \|\| ! is_array( $pre_query[0] ) ) {
243	return false;
244	}
245
246	// If the filter set the orders, make sure the others values are set as well and skip running the query.
247	list( $this->orders, $this->found_orders, $this->max_num_pages ) = $pre_query;
248
249	if ( ! is_int( $this->found_orders ) \|\| $this->found_orders < 1 ) {
250	$this->found_orders = count( $this->orders );
251	}
252
253	if ( ! is_int( $this->max_num_pages ) \|\| $this->max_num_pages < 1 ) {
254	if ( ! $this->arg_isset( 'limit' ) \|\| ! is_int( $this->args['limit'] ) \|\| $this->args['limit'] < 1 ) {
255	$this->args['limit'] = 10;
256	}
257	$this->max_num_pages = (int) ceil( $this->found_orders / $this->args['limit'] );
258	}
259
260	return true;
261	}
262
263	/**
264	* Remaps some legacy and `WP_Query` specific query vars to vars available in the customer order table scheme.
265	*
266	* @return void
267	*/
268	private function maybe_remap_args(): void {
269	$mapping = array(
270	// WP_Query legacy.
271	'post_date' => 'date_created',
272	'post_date_gmt' => 'date_created_gmt',
273	'post_modified' => 'date_updated',
274	'post_modified_gmt' => 'date_updated_gmt',
275	'post_status' => 'status',
276	'_date_completed' => 'date_completed',
277	'_date_paid' => 'date_paid',
278	'paged' => 'page',
279	'post_parent' => 'parent_order_id',
280	'post_parent__in' => 'parent_order_id',
281	'post_parent__not_in' => 'parent_exclude',
282	'post__not_in' => 'exclude',
283	'posts_per_page' => 'limit',
284	'p' => 'id',
285	'post__in' => 'id',
286	'post_type' => 'type',
287	'fields' => 'return',
288
289	'customer_user' => 'customer_id',
290	'order_currency' => 'currency',
291	'order_version' => 'woocommerce_version',
292	'cart_discount' => 'discount_total_amount',
293	'cart_discount_tax' => 'discount_tax_amount',
294	'order_shipping' => 'shipping_total_amount',
295	'order_shipping_tax' => 'shipping_tax_amount',
296	'order_tax' => 'tax_amount',
297
298	// Translate from WC_Order_Query to table structure.
299	'version' => 'woocommerce_version',
300	'date_modified' => 'date_updated',
301	'date_modified_gmt' => 'date_updated_gmt',
302	'discount_total' => 'discount_total_amount',
303	'discount_tax' => 'discount_tax_amount',
304	'shipping_total' => 'shipping_total_amount',
305	'shipping_tax' => 'shipping_tax_amount',
306	'cart_tax' => 'tax_amount',
307	'total' => 'total_amount',
308	'customer_ip_address' => 'ip_address',
309	'customer_user_agent' => 'user_agent',
310	'parent' => 'parent_order_id',
311	);
312
313	foreach ( $mapping as $query_key => $table_field ) {
314	if ( isset( $this->args[ $query_key ] ) && '' !== $this->args[ $query_key ] ) {
315	$this->args[ $table_field ] = $this->args[ $query_key ];
316	unset( $this->args[ $query_key ] );
317	}
318	}
319
320	// meta_query.
321	$this->args['meta_query'] = ( $this->arg_isset( 'meta_query' ) && is_array( $this->args['meta_query'] ) ) ? $this->args['meta_query'] : array(); // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
322
323	$shortcut_meta_query = array();
324	foreach ( array( 'key', 'value', 'compare', 'type', 'compare_key', 'type_key' ) as $key ) {
325	if ( $this->arg_isset( "meta_{$key}" ) ) {
326	$shortcut_meta_query[ $key ] = $this->args[ "meta_{$key}" ];
327	}
328	}
329
330	if ( ! empty( $shortcut_meta_query ) ) {
331	if ( ! empty( $this->args['meta_query'] ) ) {
332	$this->args['meta_query'] = array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
333	'relation' => 'AND',
334	$shortcut_meta_query,
335	$this->args['meta_query'],
336	);
337	} else {
338	$this->args['meta_query'] = array( $shortcut_meta_query ); // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
339	}
340	}
341	}
342
343	/**
344	* Generates a `WP_Date_Query` compatible query from a given date.
345	* YYYY-MM-DD queries have 'day' precision for backwards compatibility.
346	*
347	* @param mixed $date The date. Can be a {@see \WC_DateTime}, a timestamp or a string.
348	* @return array An array with keys 'year', 'month', 'day' and possibly 'hour', 'minute' and 'second'.
349	*/
350	private function date_to_date_query_arg( $date ): array {
351	$result = array(
352	'year' => '',
353	'month' => '',
354	'day' => '',
355	);
356
357	$precision = null;
358	if ( is_numeric( $date ) ) {
359	$date = new \WC_DateTime( "@{$date}", new \DateTimeZone( 'UTC' ) );
360	$precision = 'second';
361	} elseif ( ! is_a( $date, 'WC_DateTime' ) ) {
362	// For backwards compat (see https://developer.woocommerce.com/docs/extensions/core-concepts/wc-get-orders/#date)
363	// only YYYY-MM-DD is considered for date values. Timestamps do support second precision.
364	$date = wc_string_to_datetime( date( 'Y-m-d', strtotime( $date ) ) );
365	$precision = 'day';
366	}
367
368	$result['year'] = $date->date( 'Y' );
369	$result['month'] = $date->date( 'm' );
370	$result['day'] = $date->date( 'd' );
371
372	if ( 'second' === $precision ) {
373	$result['hour'] = $date->date( 'H' );
374	$result['minute'] = $date->date( 'i' );
375	$result['second'] = $date->date( 's' );
376	}
377
378	return $result;
379	}
380
381	/**
382	* Returns UTC-based date query arguments for a combination of local time dates and a date shorthand operator.
383	*
384	* @param array $dates_raw Array of dates (in local time) to use in combination with the operator.
385	* @param string $operator One of the operators supported by date queries (<, <=, =, ..., >, >=).
386	* @return array Partial date query arg with relevant dates now UTC-based.
387	*
388	* @throws \Exception If an invalid date shorthand operator is specified.
389	*
390	* @since 8.2.0
391	*/
392	private function local_time_to_gmt_date_query( $dates_raw, $operator ) {
393	$result = array();
394
395	// Convert YYYY-MM-DD to UTC timestamp. Per https://developer.woocommerce.com/docs/extensions/core-concepts/wc-get-orders/#date only date is relevant (time is ignored).
396	foreach ( $dates_raw as &$raw_date ) {
397	$raw_date = is_numeric( $raw_date ) ? $raw_date : strtotime( get_gmt_from_date( date( 'Y-m-d', strtotime( $raw_date ) ) ) );
398	}
399
400	$date1 = end( $dates_raw );
401
402	switch ( $operator ) {
403	case '>':
404	$result = array(
405	'after' => $this->date_to_date_query_arg( $date1 + DAY_IN_SECONDS ),
406	'inclusive' => true,
407	);
408	break;
409	case '>=':
410	$result = array(
411	'after' => $this->date_to_date_query_arg( $date1 ),
412	'inclusive' => true,
413	);
414	break;
415	case '=':
416	$result = array(
417	'relation' => 'AND',
418	array(
419	'after' => $this->date_to_date_query_arg( $date1 ),
420	'inclusive' => true,
421	),
422	array(
423	'before' => $this->date_to_date_query_arg( $date1 + DAY_IN_SECONDS ),
424	'inclusive' => false,
425	),
426	);
427	break;
428	case '<=':
429	$result = array(
430	'before' => $this->date_to_date_query_arg( $date1 + DAY_IN_SECONDS ),
431	'inclusive' => false,
432	);
433	break;
434	case '<':
435	$result = array(
436	'before' => $this->date_to_date_query_arg( $date1 ),
437	'inclusive' => false,
438	);
439	break;
440	case '...':
441	$result = array(
442	'relation' => 'AND',
443	$this->local_time_to_gmt_date_query( array( $dates_raw[1] ), '<=' ),
444	$this->local_time_to_gmt_date_query( array( $dates_raw[0] ), '>=' ),
445	);
446
447	break;
448	}
449
450	if ( ! $result ) {
451	throw new \Exception( 'Please specify a valid date shorthand operator.' );
452	}
453
454	return $result;
455	}
456
457	/**
458	* Processes date-related query args and merges the result into 'date_query'.
459	*
460	* @return void
461	* @throws \Exception When date args are invalid.
462	*/
463	private function process_date_args(): void {
464	if ( $this->arg_isset( 'date_query' ) ) {
465	// Process already passed date queries args.
466	$this->args['date_query'] = $this->map_gmt_and_post_keys_to_hpos_keys( $this->args['date_query'] );
467	}
468
469	$valid_operators = array( '>', '>=', '=', '<=', '<', '...' );
470	$date_queries = array();
471	$local_to_gmt_date_keys = array(
472	'date_created' => 'date_created_gmt',
473	'date_updated' => 'date_updated_gmt',
474	'date_paid' => 'date_paid_gmt',
475	'date_completed' => 'date_completed_gmt',
476	);
477
478	$gmt_date_keys = array_values( $local_to_gmt_date_keys );
479	$local_date_keys = array_keys( $local_to_gmt_date_keys );
480
481	$valid_date_keys = array_merge( $gmt_date_keys, $local_date_keys );
482	$date_keys = array_filter( $valid_date_keys, array( $this, 'arg_isset' ) );
483
484	foreach ( $date_keys as $date_key ) {
485	$is_local = in_array( $date_key, $local_date_keys, true );
486	$date_value = $this->args[ $date_key ];
487	$operator = '=';
488	$dates_raw = array();
489	$dates = array();
490
491	if ( is_string( $date_value ) && preg_match( self::REGEX_SHORTHAND_DATES, $date_value, $matches ) ) {
492	$operator = in_array( $matches[2], $valid_operators, true ) ? $matches[2] : '';
493
494	if ( ! empty( $matches[1] ) ) {
495	$dates_raw[] = $matches[1];
496	}
497
498	$dates_raw[] = $matches[3];
499	} else {
500	$dates_raw[] = $date_value;
501	}
502
503	if ( empty( $dates_raw ) \|\| ! $operator \|\| ( '...' === $operator && count( $dates_raw ) < 2 ) ) {
504	throw new \Exception( 'Invalid date_query' );
505	}
506
507	if ( $is_local ) {
508	$date_key = $local_to_gmt_date_keys[ $date_key ];
509
510	if ( ! is_numeric( $dates_raw[0] ) && ( ! isset( $dates_raw[1] ) \|\| ! is_numeric( $dates_raw[1] ) ) ) {
511	// Only non-numeric args can be considered local time. Timestamps are assumed to be UTC per https://developer.woocommerce.com/docs/extensions/core-concepts/wc-get-orders/#date.
512	$date_queries[] = array_merge(
513	array(
514	'column' => $date_key,
515	),
516	$this->local_time_to_gmt_date_query( $dates_raw, $operator )
517	);
518
519	continue;
520	}
521	}
522
523	$operator_to_keys = array();
524
525	if ( in_array( $operator, array( '>', '>=', '...' ), true ) ) {
526	$operator_to_keys[] = 'after';
527	}
528
529	if ( in_array( $operator, array( '<', '<=', '...' ), true ) ) {
530	$operator_to_keys[] = 'before';
531	}
532
533	$dates = array_map( array( $this, 'date_to_date_query_arg' ), $dates_raw );
534	$date_queries[] = array_merge(
535	array(
536	'column' => $date_key,
537	'inclusive' => ! in_array( $operator, array( '<', '>' ), true ),
538	),
539	'=' === $operator
540	? end( $dates )
541	: array_combine( $operator_to_keys, $dates )
542	);
543	}
544
545	// Add top-level date parameters to the date_query.
546	$tl_query = array();
547	foreach ( array( 'hour', 'minute', 'second', 'year', 'monthnum', 'week', 'day', 'year' ) as $tl_key ) {
548	if ( $this->arg_isset( $tl_key ) ) {
549	$tl_query[ $tl_key ] = $this->args[ $tl_key ];
550	unset( $this->args[ $tl_key ] );
551	}
552	}
553
554	if ( $tl_query ) {
555	$tl_query['column'] = 'date_created_gmt';
556	$date_queries[] = $tl_query;
557	}
558
559	if ( $date_queries ) {
560	if ( ! $this->arg_isset( 'date_query' ) ) {
561	$this->args['date_query'] = array();
562	}
563
564	$this->args['date_query'] = array_merge(
565	array( 'relation' => 'AND' ),
566	$date_queries,
567	$this->args['date_query']
568	);
569	}
570
571	$this->process_date_query_columns();
572	}
573
574	/**
575	* Helper function to map posts and gmt based keys to HPOS keys.
576	*
577	* @param array $query Date query argument.
578	*
579	* @return array\|mixed Date query argument with modified keys.
580	*/
581	private function map_gmt_and_post_keys_to_hpos_keys( $query ) {
582	if ( ! is_array( $query ) ) {
583	return $query;
584	}
585
586	$post_to_hpos_mappings = array(
587	'post_date' => 'date_created',
588	'post_date_gmt' => 'date_created_gmt',
589	'post_modified' => 'date_updated',
590	'post_modified_gmt' => 'date_updated_gmt',
591	'_date_completed' => 'date_completed',
592	'_date_paid' => 'date_paid',
593	'date_modified' => 'date_updated',
594	'date_modified_gmt' => 'date_updated_gmt',
595	);
596
597	$local_to_gmt_date_keys = array(
598	'date_created' => 'date_created_gmt',
599	'date_updated' => 'date_updated_gmt',
600	'date_paid' => 'date_paid_gmt',
601	'date_completed' => 'date_completed_gmt',
602	);
603
604	array_walk(
605	$query,
606	function ( &$sub_query ) {
607	$sub_query = $this->map_gmt_and_post_keys_to_hpos_keys( $sub_query );
608	}
609	);
610
611	if ( ! isset( $query['column'] ) ) {
612	return $query;
613	}
614
615	if ( isset( $post_to_hpos_mappings[ $query['column'] ] ) ) {
616	$query['column'] = $post_to_hpos_mappings[ $query['column'] ];
617	}
618
619	// Convert any local dates to GMT.
620	if ( isset( $local_to_gmt_date_keys[ $query['column'] ] ) ) {
621	$query['column'] = $local_to_gmt_date_keys[ $query['column'] ];
622	$op = isset( $query['after'] ) ? 'after' : 'before';
623	$date_value_local = $query[ $op ];
624	$date_value_gmt = wc_string_to_timestamp( get_gmt_from_date( wc_string_to_datetime( $date_value_local ) ) );
625	$query[ $op ] = $this->date_to_date_query_arg( $date_value_gmt );
626	}
627
628	return $query;
629	}
630
631	/**
632	* Makes sure all 'date_query' columns are correctly prefixed and their respective tables are being JOIN'ed.
633	*
634	* @return void
635	*/
636	private function process_date_query_columns() {
637	global $wpdb;
638
639	$legacy_columns = array(
640	'post_date' => 'date_created_gmt',
641	'post_date_gmt' => 'date_created_gmt',
642	'post_modified' => 'date_modified_gmt',
643	'post_modified_gmt' => 'date_updated_gmt',
644	);
645	$table_mapping = array(
646	'date_created_gmt' => $this->tables['orders'],
647	'date_updated_gmt' => $this->tables['orders'],
648	'date_paid_gmt' => $this->tables['operational_data'],
649	'date_completed_gmt' => $this->tables['operational_data'],
650	);
651
652	if ( empty( $this->args['date_query'] ) ) {
653	return;
654	}
655
656	array_walk_recursive(
657	$this->args['date_query'],
658	function ( &$value, $key ) use ( $legacy_columns, $table_mapping, $wpdb ) {
659	if ( 'column' !== $key ) {
660	return;
661	}
662
663	// Translate legacy columns from wp_posts if necessary.
664	$value =
665	( isset( $legacy_columns[ $value ] ) \|\| isset( $legacy_columns[ "{$wpdb->posts}.{$value}" ] ) )
666	? $legacy_columns[ $value ]
667	: $value;
668
669	$table = $table_mapping[ $value ] ?? null;
670
671	if ( ! $table ) {
672	return;
673	}
674
675	$value = "{$table}.{$value}";
676
677	if ( $table !== $this->tables['orders'] ) {
678	$this->join( $table, '', '', 'inner', true );
679	}
680	}
681	);
682	}
683
684	/**
685	* Sanitizes the 'status' query var.
686	*
687	* @return void
688	*/
689	private function sanitize_status(): void {
690	$valid_statuses = array_keys( wc_get_order_statuses() );
691
692	if ( empty( $this->args['status'] ) ) {
693	$this->args['status'] = array();
694	}
695
696	if ( ! is_array( $this->args['status'] ) ) {
697	$this->args['status'] = array( $this->args['status'] );
698	}
699
700	if ( empty( $this->args['status'] ) \|\| in_array( 'any', $this->args['status'], true ) ) {
701	// Querying for 'any' status or empty status, filter to valid statuses from wc_get_order_statuses(),
702	// excluding statuses marked as exclude_from_search (e.g. checkout-draft) to match WP_Query behavior.
703	$exclude = get_post_stati( array( 'exclude_from_search' => true ) );
704	$this->args['status'] = array_diff( $valid_statuses, $exclude );
705	} elseif ( in_array( 'all', $this->args['status'], true ) ) {
706	// Querying for 'all' status does not filter by status at all.
707	$this->args['status'] = array();
708	}
709
710	foreach ( $this->args['status'] as &$status ) {
711	$status = in_array( 'wc-' . $status, $valid_statuses, true ) ? 'wc-' . $status : $status;
712	}
713
714	$this->args['status'] = array_unique( array_filter( $this->args['status'] ) );
715	}
716
717	/**
718	* Parses and sanitizes the 'orderby' query var.
719	*
720	* @param string\|array $orderby The unsanitized orderby param which can be a string or an array of orderby keys and direction (ASC, DESC).
721	* @return string\|array The sanitized orderby param which can be a string or an array of orderby keys and direction (ASC, DESC).
722	*/
723	private function sanitize_order_orderby( $orderby ) {
724	// No need to sanitize, will be processed in calling function.
725	if ( 'include' === $orderby \|\| 'post__in' === $orderby \|\| 'none' === $orderby ) {
726	return $orderby;
727	}
728
729	// Translate $orderby to a valid field.
730	$mapping = array(
731	'ID' => "{$this->tables['orders']}.id",
732	'id' => "{$this->tables['orders']}.id",
733	'type' => "{$this->tables['orders']}.type",
734	'date' => "{$this->tables['orders']}.date_created_gmt",
735	'date_created' => "{$this->tables['orders']}.date_created_gmt",
736	'modified' => "{$this->tables['orders']}.date_updated_gmt",
737	'date_modified' => "{$this->tables['orders']}.date_updated_gmt",
738	'parent' => "{$this->tables['orders']}.parent_order_id",
739	'total' => "{$this->tables['orders']}.total_amount",
740	'order_total' => "{$this->tables['orders']}.total_amount",
741	);
742
743	$order = $this->sanitize_order( $this->args['order'] ?? '' );
744	$allowed_orderby = array_merge( array_keys( $mapping ), array_values( $mapping ), $this->meta_query ? $this->meta_query->get_orderby_keys() : array() );
745
746	// Convert string orderby to an array of orderby keys and direction (ASC, DESC).
747	if ( is_string( $orderby ) ) {
748	$orderby_fields = array_map( 'trim', explode( ' ', $orderby ) );
749	$orderby = array();
750	foreach ( $orderby_fields as $field ) {
751	$orderby[ $field ] = $order;
752	}
753	}
754
755	$sanitized_orderby = array();
756
757	foreach ( $orderby as $order_key => $order ) {
758	if ( ! in_array( $order_key, $allowed_orderby, true ) ) {
759	continue;
760	}
761
762	if ( isset( $mapping[ $order_key ] ) ) {
763	$order_key = $mapping[ $order_key ];
764	}
765
766	$sanitized_orderby[ $order_key ] = $this->sanitize_order( $order );
767	}
768
769	return $sanitized_orderby;
770	}
771
772	/**
773	* Makes sure the order in an ORDER BY statement is either 'ASC' o 'DESC'.
774	*
775	* @param string $order The unsanitized order.
776	* @return string The sanitized order.
777	*/
778	private function sanitize_order( string $order ): string {
779	$order = strtoupper( $order );
780
781	return in_array( $order, array( 'ASC', 'DESC' ), true ) ? $order : 'DESC';
782	}
783
784	/**
785	* Builds the final SQL query to be run.
786	*
787	* @return void
788	*/
789	private function build_query(): void {
790	$this->maybe_remap_args();
791
792	// Field queries.
793	if ( ! empty( $this->args['field_query'] ) ) {
794	$this->field_query = new OrdersTableFieldQuery( $this );
795	$sql = $this->field_query->get_sql_clauses();
796	$this->join = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
797	$this->where = $sql['where'] ? array_merge( $this->where, $sql['where'] ) : $this->where;
798	}
799
800	// Build query.
801	$this->process_date_args();
802	$this->process_orders_table_query_args();
803	$this->process_operational_data_table_query_args();
804	$this->process_addresses_table_query_args();
805
806	// Search queries.
807	if ( ! empty( $this->args['s'] ) ) {
808	$this->search_query = new OrdersTableSearchQuery( $this );
809	$sql = $this->search_query->get_sql_clauses();
810	$this->join = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
811	$this->where = $sql['where'] ? array_merge( $this->where, $sql['where'] ) : $this->where;
812	}
813
814	// Meta queries.
815	if ( ! empty( $this->args['meta_query'] ) ) {
816	$this->meta_query = new OrdersTableMetaQuery( $this );
817
818	$sql = $this->meta_query->get_sql_clauses();
819
820	$this->join = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
821	$this->where = $sql['where'] ? array_merge( $this->where, array( $sql['where'] ) ) : $this->where;
822
823	}
824
825	// Date queries.
826	if ( ! empty( $this->args['date_query'] ) ) {
827	$this->date_query = new \WP_Date_Query( $this->args['date_query'], "{$this->tables['orders']}.date_created_gmt" );
828	$this->where[] = substr( trim( $this->date_query->get_sql() ), 3 ); // WP_Date_Query includes "AND".
829	}
830
831	$this->process_orderby();
832	$this->process_limit();
833
834	$orders_table = $this->tables['orders'];
835
836	// Group by is a faster substitute for DISTINCT, as long as we are only selecting IDs. MySQL don't like it when we join tables and use DISTINCT.
837	$this->groupby[] = "{$this->tables['orders']}.id";
838	$this->fields = "{$orders_table}.id";
839	$fields = $this->fields;
840
841	// JOIN.
842	$join = implode( ' ', array_unique( array_filter( array_map( 'trim', $this->join ) ) ) );
843
844	// WHERE.
845	$where = '1=1';
846	foreach ( $this->where as $_where ) {
847	if ( strlen( $_where ) > 0 ) {
848	$where .= " AND ({$_where})";
849	}
850	}
851
852	// ORDER BY.
853	$orderby = $this->orderby ? implode( ', ', $this->orderby ) : '';
854
855	// LIMITS.
856	$limits = '';
857
858	if ( ! empty( $this->limits ) && count( $this->limits ) === 2 ) {
859	list( $offset, $row_count ) = $this->limits;
860	$row_count = -1 === $row_count ? self::MYSQL_MAX_UNSIGNED_BIGINT : (int) $row_count;
861	$limits = 'LIMIT ' . (int) $offset . ', ' . $row_count;
862	}
863
864	// GROUP BY.
865	$groupby = $this->groupby ? implode( ', ', (array) $this->groupby ) : '';
866
867	$pieces = compact( 'fields', 'join', 'where', 'groupby', 'orderby', 'limits' );
868
869	if ( ! $this->suppress_filters ) {
870	/**
871	* Filters all query clauses at once.
872	* Covers the fields (SELECT), JOIN, WHERE, GROUP BY, ORDER BY, and LIMIT clauses.
873	*
874	* @since 7.9.0
875	*
876	* @param string[] $clauses {
877	* Associative array of the clauses for the query.
878	*
879	* @type string $fields The SELECT clause of the query.
880	* @type string $join The JOIN clause of the query.
881	* @type string $where The WHERE clause of the query.
882	* @type string $groupby The GROUP BY clause of the query.
883	* @type string $orderby The ORDER BY clause of the query.
884	* @type string $limits The LIMIT clause of the query.
885	* }
886	* @param OrdersTableQuery $query The OrdersTableQuery instance (passed by reference).
887	* @param array $args Query args.
888	*/
889	$clauses = (array) apply_filters_ref_array( 'woocommerce_orders_table_query_clauses', array( $pieces, &$this, $this->args ) );
890
891	$fields = $clauses['fields'] ?? '';
892	$join = $clauses['join'] ?? '';
893	$where = $clauses['where'] ?? '';
894	$groupby = $clauses['groupby'] ?? '';
895	$orderby = $clauses['orderby'] ?? '';
896	$limits = $clauses['limits'] ?? '';
897	}
898
899	$groupby = $groupby ? ( 'GROUP BY ' . $groupby ) : '';
900	$orderby = $orderby ? ( 'ORDER BY ' . $orderby ) : '';
901
902	$this->sql = "SELECT $fields FROM $orders_table $join WHERE $where $groupby $orderby $limits";
903
904	if ( ! $this->suppress_filters ) {
905	/**
906	* Filters the completed SQL query.
907	*
908	* @since 7.9.0
909	*
910	* @param string $sql The complete SQL query.
911	* @param OrdersTableQuery $query The OrdersTableQuery instance (passed by reference).
912	* @param array $args Query args.
913	*/
914	$this->sql = apply_filters_ref_array( 'woocommerce_orders_table_query_sql', array( $this->sql, &$this, $this->args ) );
915	}
916
917	$this->build_count_query( $fields, $join, $where, $groupby );
918	}
919
920	/**
921	* Build SQL query for counting total number of results.
922	*
923	* @param string $fields Prepared fields for SELECT clause.
924	* @param string $join Prepared JOIN clause.
925	* @param string $where Prepared WHERE clause.
926	* @param string $groupby Prepared GROUP BY clause.
927	*/
928	private function build_count_query( $fields, $join, $where, $groupby ) {
929	if ( ! isset( $this->sql ) \|\| '' === $this->sql ) {
930	wc_doing_it_wrong( __FUNCTION__, 'Count query can only be build after main query is built.', '7.3.0' );
931	}
932	$orders_table = $this->tables['orders'];
933	$count_fields = "COUNT(DISTINCT $fields)";
934	if ( "{$orders_table}.id" === $fields && '' === $join ) {
935	// DISTINCT adds performance overhead, exclude the DISTINCT function when confident it is not needed.
936	$count_fields = 'COUNT(*)';
937	}
938	$this->count_sql = "SELECT $count_fields FROM $orders_table $join WHERE $where";
939
940	if ( ! $this->suppress_filters ) {
941	/**
942	* Filters the count SQL query.
943	*
944	* @since 8.6.0
945	*
946	* @param string $sql The count SQL query.
947	* @param OrdersTableQuery $query The OrdersTableQuery instance (passed by reference).
948	* @param array $args Query args.
949	* @param string $fields Prepared fields for SELECT clause.
950	* @param string $join Prepared JOIN clause.
951	* @param string $where Prepared WHERE clause.
952	* @param string $groupby Prepared GROUP BY clause.
953	*/
954	$this->count_sql = apply_filters_ref_array( 'woocommerce_orders_table_query_count_sql', array( $this->count_sql, &$this, $this->args, $fields, $join, $where, $groupby ) );
955	}
956	}
957
958	/**
959	* Returns the table alias for a given table mapping.
960	*
961	* @param string $mapping_id The mapping name (e.g. 'orders' or 'operational_data').
962	* @return string Table alias.
963	*
964	* @since 7.0.0
965	*/
966	public function get_core_mapping_alias( string $mapping_id ): string {
967	return in_array( $mapping_id, array( 'billing_address', 'shipping_address' ), true )
968	? $mapping_id
969	: $this->tables[ $mapping_id ];
970	}
971
972	/**
973	* Returns an SQL JOIN clause that can be used to join the main orders table with another order table.
974	*
975	* @param string $mapping_id The mapping name (e.g. 'orders' or 'operational_data').
976	* @return string The JOIN clause.
977	*
978	* @since 7.0.0
979	*/
980	public function get_core_mapping_join( string $mapping_id ): string {
981	global $wpdb;
982
983	if ( 'orders' === $mapping_id ) {
984	return '';
985	}
986
987	$is_address_mapping = in_array( $mapping_id, array( 'billing_address', 'shipping_address' ), true );
988
989	$alias = $this->get_core_mapping_alias( $mapping_id );
990	$table = $is_address_mapping ? $this->tables['addresses'] : $this->tables[ $mapping_id ];
991	$join = '';
992	$join_on = '';
993
994	$join .= "INNER JOIN `{$table}`" . ( $alias !== $table ? " AS `{$alias}`" : '' );
995
996	if ( isset( $this->mappings[ $mapping_id ]['order_id'] ) ) {
997	$join_on .= "`{$this->tables['orders']}`.id = `{$alias}`.order_id";
998	}
999
1000	if ( $is_address_mapping ) {
1001	$join_on .= $wpdb->prepare( " AND `{$alias}`.address_type = %s", substr( $mapping_id, 0, -8 ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
1002	}
1003
1004	return $join . ( $join_on ? " ON ( {$join_on} )" : '' );
1005	}
1006
1007	/**
1008	* JOINs the main orders table with another table.
1009	*
1010	* @param string $table Table name (including prefix).
1011	* @param string $alias Table alias to use. Defaults to $table.
1012	* @param string $on ON clause. Defaults to "wc_orders.id = {$alias}.order_id".
1013	* @param string $join_type JOIN type: LEFT, RIGHT or INNER.
1014	* @param boolean $alias_once If TRUE, table won't be JOIN'ed again if already JOIN'ed.
1015	* @return void
1016	* @throws \Exception When an error occurs, such as trying to re-use an alias with $alias_once = FALSE.
1017	*/
1018	private function join( string $table, string $alias = '', string $on = '', string $join_type = 'inner', bool $alias_once = false ) {
1019	$alias = empty( $alias ) ? $table : $alias;
1020	$join_type = strtoupper( trim( $join_type ) );
1021
1022	if ( $this->tables['orders'] === $alias ) {
1023	// translators: %s is a table name.
1024	throw new \Exception( sprintf( __( '%s can not be used as a table alias in OrdersTableQuery', 'woocommerce' ), $alias ) );
1025	}
1026
1027	if ( empty( $on ) ) {
1028	if ( $this->tables['orders'] === $table ) {
1029	$on = "`{$this->tables['orders']}`.id = `{$alias}`.id";
1030	} else {
1031	$on = "`{$this->tables['orders']}`.id = `{$alias}`.order_id";
1032	}
1033	}
1034
1035	if ( isset( $this->join[ $alias ] ) ) {
1036	if ( ! $alias_once ) {
1037	// translators: %s is a table name.
1038	throw new \Exception( sprintf( __( 'Can not re-use table alias "%s" in OrdersTableQuery.', 'woocommerce' ), $alias ) );
1039	}
1040
1041	return;
1042	}
1043
1044	if ( '' === $join_type \|\| ! in_array( $join_type, array( 'LEFT', 'RIGHT', 'INNER' ), true ) ) {
1045	$join_type = 'INNER';
1046	}
1047
1048	$sql_join = '';
1049	$sql_join .= "{$join_type} JOIN `{$table}` ";
1050	$sql_join .= ( $alias !== $table ) ? "AS `{$alias}` " : '';
1051	$sql_join .= "ON ( {$on} )";
1052
1053	$this->join[ $alias ] = $sql_join;
1054	}
1055
1056	/**
1057	* Generates a properly escaped and sanitized WHERE condition for a given field.
1058	*
1059	* @param string $table The table the field belongs to.
1060	* @param string $field The field or column name.
1061	* @param string $operator The operator to use in the condition. Defaults to '=' or 'IN' depending on $value.
1062	* @param mixed $value The value.
1063	* @param string $type The column type as specified in {@see OrdersTableDataStore} column mappings.
1064	* @return string The resulting WHERE condition.
1065	*/
1066	public function where( string $table, string $field, string $operator, $value, string $type ): string {
1067	global $wpdb;
1068
1069	$db_util = wc_get_container()->get( DatabaseUtil::class );
1070	$operator = strtoupper( '' !== $operator ? $operator : '=' );
1071
1072	try {
1073	$format = $db_util->get_wpdb_format_for_type( $type );
1074	} catch ( \Exception $e ) {
1075	$format = '%s';
1076	}
1077
1078	// = and != can be shorthands for IN and NOT in for array values.
1079	if ( is_array( $value ) && '=' === $operator ) {
1080	$operator = 'IN';
1081	} elseif ( is_array( $value ) && '!=' === $operator ) {
1082	$operator = 'NOT IN';
1083	}
1084
1085	if ( ! in_array( $operator, array( '=', '!=', 'IN', 'NOT IN', '>', '>=', '<', '<=' ), true ) ) {
1086	return false;
1087	}
1088
1089	if ( is_array( $value ) ) {
1090	$value = array_map( array( $db_util, 'format_object_value_for_db' ), $value, array_fill( 0, count( $value ), $type ) );
1091	} else {
1092	$value = $db_util->format_object_value_for_db( $value, $type );
1093	}
1094
1095	if ( is_array( $value ) ) {
1096	$placeholder = array_fill( 0, count( $value ), $format );
1097	$placeholder = '(' . implode( ',', $placeholder ) . ')';
1098	} else {
1099	$placeholder = $format;
1100	}
1101
1102	$sql = $wpdb->prepare( "{$table}.{$field} {$operator} {$placeholder}", $value ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
1103
1104	return $sql;
1105	}
1106
1107	/**
1108	* Processes fields related to the orders table.
1109	*
1110	* @return void
1111	*/
1112	private function process_orders_table_query_args(): void {
1113	$this->sanitize_status();
1114
1115	$fields = array_filter(
1116	array(
1117	'id',
1118	'status',
1119	'type',
1120	'currency',
1121	'tax_amount',
1122	'customer_id',
1123	'billing_email',
1124	'parent_order_id',
1125	'payment_method',
1126	'payment_method_title',
1127	'transaction_id',
1128	'ip_address',
1129	'user_agent',
1130	),
1131	array( $this, 'arg_isset' )
1132	);
1133
1134	foreach ( $fields as $arg_key ) {
1135	$this->where[] = $this->where( $this->tables['orders'], $arg_key, '=', $this->args[ $arg_key ], $this->mappings['orders'][ $arg_key ]['type'] );
1136	}
1137
1138	// customer_note allows empty string to match orders with no note, so it cannot use arg_isset (which skips '').
1139	if ( isset( $this->args['customer_note'] ) ) {
1140	$this->where[] = $this->where( $this->tables['orders'], 'customer_note', '=', $this->args['customer_note'], $this->mappings['orders']['customer_note']['type'] );
1141	}
1142
1143	if ( $this->arg_isset( 'parent_exclude' ) ) {
1144	$this->where[] = $this->where( $this->tables['orders'], 'parent_order_id', '!=', $this->args['parent_exclude'], 'int' );
1145	}
1146
1147	if ( $this->arg_isset( 'exclude' ) ) {
1148	$this->where[] = $this->where( $this->tables['orders'], 'id', '!=', $this->args['exclude'], 'int' );
1149	}
1150
1151	// 'customer' is a very special field.
1152	if ( $this->arg_isset( 'customer' ) ) {
1153	$customer_query = $this->generate_customer_query( $this->args['customer'] );
1154
1155	if ( $customer_query ) {
1156	$this->where[] = $customer_query;
1157	}
1158	}
1159
1160	// Handle total filtering with operators.
1161	if ( $this->arg_isset( 'total_amount' ) ) {
1162	$total_param = $this->args['total_amount'];
1163
1164	// If it's a simple number, convert to array format.
1165	if ( is_numeric( $total_param ) ) {
1166	$total_param = array(
1167	'value' => $total_param,
1168	'operator' => '=',
1169	);
1170	}
1171
1172	$total_query = $this->generate_total_query( (array) $total_param );
1173
1174	if ( $total_query ) {
1175	$this->where[] = $total_query;
1176	}
1177	}
1178	}
1179
1180	/**
1181	* Generate SQL conditions for the 'customer' query.
1182	*
1183	* @param array $values List of customer ids or emails.
1184	* @param string $relation 'OR' or 'AND' relation used to build the customer query.
1185	* @return string SQL to be used in a WHERE clause.
1186	*/
1187	private function generate_customer_query( $values, string $relation = 'OR' ): string {
1188	$values = is_array( $values ) ? $values : array( $values );
1189	$ids = array();
1190	$emails = array();
1191	$pieces = array();
1192	foreach ( $values as $value ) {
1193	if ( is_array( $value ) ) {
1194	$sql = $this->generate_customer_query( $value, 'AND' );
1195	$pieces[] = $sql ? '(' . $sql . ')' : '';
1196	} elseif ( is_numeric( $value ) ) {
1197	$ids[] = absint( $value );
1198	} elseif ( is_string( $value ) && is_email( $value ) ) {
1199	$emails[] = sanitize_email( $value );
1200	} else {
1201	// Invalid query.
1202	$pieces[] = '1=0';
1203	}
1204	}
1205
1206	if ( $ids ) {
1207	$pieces[] = $this->where( $this->tables['orders'], 'customer_id', '=', $ids, 'int' );
1208	}
1209
1210	if ( $emails ) {
1211	$pieces[] = $this->where( $this->tables['orders'], 'billing_email', '=', $emails, 'string' );
1212	}
1213
1214	return $pieces ? implode( " $relation ", $pieces ) : '';
1215	}
1216
1217	/**
1218	* Generate SQL conditions for the 'total' query with operators.
1219	*
1220	* @param array $total_params Total query parameters with value, operator.
1221	* @return string SQL to be used in a WHERE clause.
1222	*/
1223	private function generate_total_query( array $total_params ): string {
1224	if ( ! isset( $total_params['value'] ) ) {
1225	return '';
1226	}
1227
1228	$operator = $total_params['operator'] ?? '=';
1229	$value = $total_params['value'];
1230	$supported_operators = array( '=', '!=', '>', '>=', '<', '<=', 'BETWEEN', 'NOT BETWEEN' );
1231
1232	if ( ! in_array( $operator, $supported_operators, true ) ) {
1233	return '';
1234	}
1235
1236	// Handle between operators.
1237	if ( 'BETWEEN' === $operator \|\| 'NOT BETWEEN' === $operator ) {
1238	if ( ! is_array( $value ) \|\| count( $value ) !== 2 ) {
1239	return '';
1240	}
1241	$value1 = wc_format_decimal( $value[0], wc_get_price_decimals() );
1242	$value2 = wc_format_decimal( $value[1], wc_get_price_decimals() );
1243
1244	if ( 'BETWEEN' === $operator ) {
1245	return $this->where( $this->tables['orders'], 'total_amount', '>=', $value1, 'decimal' ) . ' AND ' . $this->where( $this->tables['orders'], 'total_amount', '<=', $value2, 'decimal' );
1246	} else {
1247	return '(' . $this->where( $this->tables['orders'], 'total_amount', '<', $value1, 'decimal' ) . ' OR ' . $this->where( $this->tables['orders'], 'total_amount', '>', $value2, 'decimal' ) . ')';
1248	}
1249	}
1250
1251	// Handle other operators - value must be a single number.
1252	if ( ! is_numeric( $value ) ) {
1253	return '';
1254	}
1255
1256	return $this->where( $this->tables['orders'], 'total_amount', $operator, wc_format_decimal( $value, wc_get_price_decimals() ), 'decimal' );
1257	}
1258
1259	/**
1260	* Processes fields related to the operational data table.
1261	*
1262	* @return void
1263	*/
1264	private function process_operational_data_table_query_args(): void {
1265	$fields = array_filter(
1266	array(
1267	'created_via',
1268	'woocommerce_version',
1269	'prices_include_tax',
1270	'order_key',
1271	'discount_total_amount',
1272	'discount_tax_amount',
1273	'shipping_total_amount',
1274	'shipping_tax_amount',
1275	),
1276	array( $this, 'arg_isset' )
1277	);
1278
1279	if ( ! $fields ) {
1280	return;
1281	}
1282
1283	$this->join(
1284	$this->tables['operational_data'],
1285	'',
1286	'',
1287	'inner',
1288	true
1289	);
1290
1291	foreach ( $fields as $arg_key ) {
1292	$this->where[] = $this->where( $this->tables['operational_data'], $arg_key, '=', $this->args[ $arg_key ], $this->mappings['operational_data'][ $arg_key ]['type'] );
1293	}
1294	}
1295
1296	/**
1297	* Processes fields related to the addresses table.
1298	*
1299	* @return void
1300	*/
1301	private function process_addresses_table_query_args(): void {
1302	global $wpdb;
1303
1304	foreach ( array( 'billing', 'shipping' ) as $address_type ) {
1305	$fields = array_filter(
1306	array(
1307	$address_type . '_first_name',
1308	$address_type . '_last_name',
1309	$address_type . '_company',
1310	$address_type . '_address_1',
1311	$address_type . '_address_2',
1312	$address_type . '_city',
1313	$address_type . '_state',
1314	$address_type . '_postcode',
1315	$address_type . '_country',
1316	$address_type . '_phone',
1317	),
1318	array( $this, 'arg_isset' )
1319	);
1320
1321	if ( ! $fields ) {
1322	continue;
1323	}
1324
1325	$this->join(
1326	$this->tables['addresses'],
1327	$address_type,
1328	$wpdb->prepare( "{$this->tables['orders']}.id = {$address_type}.order_id AND {$address_type}.address_type = %s", $address_type ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
1329	'inner',
1330	false
1331	);
1332
1333	foreach ( $fields as $arg_key ) {
1334	$column_name = str_replace( "{$address_type}_", '', $arg_key );
1335
1336	$this->where[] = $this->where(
1337	$address_type,
1338	$column_name,
1339	'=',
1340	$this->args[ $arg_key ],
1341	$this->mappings[ "{$address_type}_address" ][ $column_name ]['type']
1342	);
1343	}
1344	}
1345	}
1346
1347	/**
1348	* Generates the ORDER BY clause.
1349	*
1350	* @return void
1351	*/
1352	private function process_orderby(): void {
1353	// 'order' and 'orderby' vars.
1354	$order = $this->sanitize_order( $this->args['order'] ?? '' );
1355	$orderby = $this->sanitize_order_orderby( $this->args['orderby'] ?? 'none' );
1356
1357	// Set orderby to an empty array by default. This will also be used if sanitize_order_orderby received "none".
1358	$this->orderby = array();
1359
1360	if ( 'include' === $orderby \|\| 'post__in' === $orderby ) {
1361	$ids = $this->args['id'] ?? $this->args['includes'];
1362	if ( empty( $ids ) ) {
1363	return;
1364	}
1365	$ids = array_map( 'absint', $ids );
1366	$this->orderby = array( "FIELD( {$this->tables['orders']}.id, " . implode( ',', $ids ) . ' )' );
1367	return;
1368	}
1369
1370	if ( is_array( $orderby ) ) {
1371	$meta_orderby_keys = $this->meta_query ? $this->meta_query->get_orderby_keys() : array();
1372	$orderby_array = array();
1373
1374	foreach ( $orderby as $_orderby => $order ) {
1375	if ( in_array( $_orderby, $meta_orderby_keys, true ) ) {
1376	$_orderby = $this->meta_query->get_orderby_clause_for_key( $_orderby );
1377	}
1378
1379	$orderby_array[] = "{$_orderby} {$order}";
1380	}
1381
1382	$this->orderby = $orderby_array;
1383	}
1384	}
1385
1386	/**
1387	* Generates the limits to be used in the LIMIT clause.
1388	*
1389	* @return void
1390	*/
1391	private function process_limit(): void {
1392	$row_count = ( $this->arg_isset( 'limit' ) ? (int) $this->args['limit'] : false );
1393	$page = ( $this->arg_isset( 'page' ) ? absint( $this->args['page'] ) : 1 );
1394	$offset = ( $this->arg_isset( 'offset' ) ? absint( $this->args['offset'] ) : false );
1395
1396	// Bool false indicates no limit was specified; less than -1 means an invalid value was passed (such as -3).
1397	if ( false === $row_count \|\| $row_count < -1 ) {
1398	return;
1399	}
1400
1401	if ( false === $offset && $row_count > -1 ) {
1402	$offset = (int) ( ( $page - 1 ) * $row_count );
1403	}
1404
1405	$this->limits = array( $offset, $row_count );
1406	}
1407
1408	/**
1409	* Checks if a query var is set (i.e. not one of the "skipped values").
1410	*
1411	* @param string $arg_key Query var.
1412	* @return bool TRUE if query var is set.
1413	*/
1414	public function arg_isset( string $arg_key ): bool {
1415	return ( isset( $this->args[ $arg_key ] ) && ! in_array( $this->args[ $arg_key ], self::SKIPPED_VALUES, true ) );
1416	}
1417
1418	/**
1419	* Runs the SQL query.
1420	*
1421	* @return void
1422	*/
1423	private function run_query(): void {
1424	global $wpdb;
1425
1426	// Run query.
1427	$this->orders = array_map( 'absint', $wpdb->get_col( $this->sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
1428
1429	// Set max_num_pages and found_orders if necessary.
1430	if ( ( $this->arg_isset( 'no_found_rows' ) && $this->args['no_found_rows'] ) \|\| empty( $this->orders ) ) {
1431	return;
1432	}
1433
1434	if ( $this->limits ) {
1435	$this->found_orders = absint( $wpdb->get_var( $this->count_sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
1436	$this->max_num_pages = (int) ceil( $this->found_orders / $this->args['limit'] );
1437	} else {
1438	$this->found_orders = count( $this->orders );
1439	}
1440	}
1441
1442	/**
1443	* Make some private available for backwards compatibility.
1444	*
1445	* @param string $name Property to get.
1446	* @return mixed
1447	*/
1448	public function __get( string $name ) {
1449	switch ( $name ) {
1450	case 'found_orders':
1451	case 'found_posts':
1452	return $this->found_orders;
1453	case 'max_num_pages':
1454	return $this->max_num_pages;
1455	case 'posts':
1456	case 'orders':
1457	return $this->orders;
1458	case 'request':
1459	return $this->sql;
1460	default:
1461	break;
1462	}
1463	}
1464
1465	/**
1466	* Returns the value of one of the query arguments.
1467	*
1468	* @param string $arg_name Query var.
1469	* @return mixed
1470	*/
1471	public function get( string $arg_name ) {
1472	return $this->args[ $arg_name ] ?? null;
1473	}
1474
1475	/**
1476	* Returns the name of one of the OrdersTableDatastore tables.
1477	*
1478	* @param string $table_id Table identifier. One of 'orders', 'operational_data', 'addresses', 'meta'.
1479	* @return string The prefixed table name.
1480	* @throws \Exception When table ID is not found.
1481	*/
1482	public function get_table_name( string $table_id = '' ): string {
1483	if ( ! isset( $this->tables[ $table_id ] ) ) {
1484	// Translators: %s is a table identifier.
1485	throw new \Exception( sprintf( __( 'Invalid table id: %s.', 'woocommerce' ), $table_id ) );
1486	}
1487
1488	return $this->tables[ $table_id ];
1489	}
1490
1491	/**
1492	* Finds table and mapping information about a field or column.
1493	*
1494	* @param string $field Field to look for in `<mapping\|field_name>.<column\|field_name>` format or just `<field_name>`.
1495	* @return false\|array {
1496	* @type string $table Full table name where the field is located.
1497	* @type string $mapping_id Unprefixed table or mapping name.
1498	* @type string $field_name Name of the corresponding order field.
1499	* @type string $column Column in $table that corresponds to the field.
1500	* @type string $type Field type.
1501	* }
1502	*/
1503	public function get_field_mapping_info( $field ) {
1504	global $wpdb;
1505
1506	$result = array(
1507	'table' => '',
1508	'mapping_id' => '',
1509	'field_name' => '',
1510	'column' => '',
1511	'column_type' => '',
1512	);
1513
1514	$mappings_to_search = array();
1515
1516	if ( false !== strstr( $field, '.' ) ) {
1517	list( $mapping_or_table, $field_name_or_col ) = explode( '.', $field );
1518
1519	$mapping_or_table = substr( $mapping_or_table, 0, strlen( $wpdb->prefix ) ) === $wpdb->prefix ? substr( $mapping_or_table, strlen( $wpdb->prefix ) ) : $mapping_or_table;
1520	$mapping_or_table = 'wc_' === substr( $mapping_or_table, 0, 3 ) ? substr( $mapping_or_table, 3 ) : $mapping_or_table;
1521
1522	if ( isset( $this->mappings[ $mapping_or_table ] ) ) {
1523	if ( isset( $this->mappings[ $mapping_or_table ][ $field_name_or_col ] ) ) {
1524	$result['mapping_id'] = $mapping_or_table;
1525	$result['column'] = $field_name_or_col;
1526	} else {
1527	$mappings_to_search = array( $mapping_or_table );
1528	}
1529	}
1530	} else {
1531	$field_name_or_col = $field;
1532	$mappings_to_search = array_keys( $this->mappings );
1533	}
1534
1535	foreach ( $mappings_to_search as $mapping_id ) {
1536	foreach ( $this->mappings[ $mapping_id ] as $column_name => $column_data ) {
1537	if ( isset( $column_data['name'] ) && $column_data['name'] === $field_name_or_col ) {
1538	$result['mapping_id'] = $mapping_id;
1539	$result['column'] = $column_name;
1540	break 2;
1541	}
1542	}
1543	}
1544
1545	if ( ! $result['mapping_id'] \|\| ! $result['column'] ) {
1546	return false;
1547	}
1548
1549	$field_info = $this->mappings[ $result['mapping_id'] ][ $result['column'] ];
1550
1551	$result['field_name'] = $field_info['name'];
1552	$result['column_type'] = $field_info['type'];
1553	$result['table'] = ( in_array( $result['mapping_id'], array( 'billing_address', 'shipping_address' ), true ) )
1554	? $this->tables['addresses']
1555	: $this->tables[ $result['mapping_id'] ];
1556
1557	return $result;
1558	}
1559
1560	/**
1561	* Return the query args that were used to initialize the query.
1562	*
1563	* @since 9.8.0
1564	* @return array Query args.
1565	*/
1566	public function get_query_args(): array {
1567	return $this->query_args;
1568	}
1569	}
1570