File "OrdersTableQuery.php"
Full Path: /home/jlklyejr/public_html/wp-content-20241030122153/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableQuery.php
File size: 44.64 KB
MIME-type: text/x-php
Charset: utf-8
<?php
// phpcs:disable Generic.Commenting.Todo.TaskFound
/**
* OrdersTableQuery class file.
*/
namespace Automattic\WooCommerce\Internal\DataStores\Orders;
use Automattic\WooCommerce\Internal\Utilities\DatabaseUtil;
defined( 'ABSPATH' ) || exit;
/**
* This class provides a `WP_Query`-like interface to custom order tables.
*
* @property-read int $found_orders Number of found orders.
* @property-read int $found_posts Alias of the `$found_orders` property.
* @property-read int $max_num_pages Max number of pages matching the current query.
* @property-read array $orders Order objects, or order IDs.
* @property-read array $posts Alias of the $orders property.
*/
class OrdersTableQuery {
/**
* Values to ignore when parsing query arguments.
*/
public const SKIPPED_VALUES = array( '', array(), null );
/**
* Regex used to catch "shorthand" comparisons in date-related query args.
*/
public const REGEX_SHORTHAND_DATES = '/([^.<>]*)(>=|<=|>|<|\.\.\.)([^.<>]+)/';
/**
* Highest possible unsigned bigint value (unsigned bigints being the type of the `id` column).
*
* This is deliberately held as a string, rather than a numeric type, for inclusion within queries.
*/
private const MYSQL_MAX_UNSIGNED_BIGINT = '18446744073709551615';
/**
* Names of all COT tables (orders, addresses, operational_data, meta) in the form 'table_id' => 'table name'.
*
* @var array
*/
private $tables = array();
/**
* Column mappings for all COT tables.
*
* @var array
*/
private $mappings = array();
/**
* Query vars after processing and sanitization.
*
* @var array
*/
private $args = array();
/**
* Columns to be selected in the SELECT clause.
*
* @var array
*/
private $fields = array();
/**
* Array of table aliases and conditions used to compute the JOIN clause of the query.
*
* @var array
*/
private $join = array();
/**
* Array of fields and conditions used to compute the WHERE clause of the query.
*
* @var array
*/
private $where = array();
/**
* Field to be used in the GROUP BY clause of the query.
*
* @var array
*/
private $groupby = array();
/**
* Array of fields used to compute the ORDER BY clause of the query.
*
* @var array
*/
private $orderby = array();
/**
* Limits used to compute the LIMIT clause of the query.
*
* @var array
*/
private $limits = array();
/**
* Results (order IDs) for the current query.
*
* @var array
*/
private $orders = array();
/**
* Final SQL query to run after processing of args.
*
* @var string
*/
private $sql = '';
/**
* Final SQL query to count results after processing of args.
*
* @var string
*/
private $count_sql = '';
/**
* The number of pages (when pagination is enabled).
*
* @var int
*/
private $max_num_pages = 0;
/**
* The number of orders found.
*
* @var int
*/
private $found_orders = 0;
/**
* Field query parser.
*
* @var OrdersTableFieldQuery
*/
private $field_query = null;
/**
* Meta query parser.
*
* @var OrdersTableMetaQuery
*/
private $meta_query = null;
/**
* Search query parser.
*
* @var OrdersTableSearchQuery?
*/
private $search_query = null;
/**
* Date query parser.
*
* @var WP_Date_Query
*/
private $date_query = null;
/**
* Instance of the OrdersTableDataStore class.
*
* @var OrdersTableDataStore
*/
private $order_datastore = null;
/**
* Whether to run filters to modify the query or not.
*
* @var boolean
*/
private $suppress_filters = false;
/**
* Sets up and runs the query after processing arguments.
*
* @param array $args Array of query vars.
*/
public function __construct( $args = array() ) {
// 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.
$this->order_datastore = wc_get_container()->get( OrdersTableDataStore::class );
$this->tables = $this->order_datastore::get_all_table_names_with_id();
$this->mappings = $this->order_datastore->get_all_order_column_mappings();
$this->suppress_filters = array_key_exists( 'suppress_filters', $args ) ? (bool) $args['suppress_filters'] : false;
unset( $args['suppress_filters'] );
$this->args = $args;
// TODO: args to be implemented.
unset( $this->args['customer_note'], $this->args['name'] );
$this->build_query();
if ( ! $this->maybe_override_query() ) {
$this->run_query();
}
}
/**
* Lets the `woocommerce_hpos_pre_query` filter override the query.
*
* @return boolean Whether the query was overridden or not.
*/
private function maybe_override_query(): bool {
/**
* Filters the orders array before the query takes place.
*
* Return a non-null value to bypass the HPOS default order queries.
*
* If the query includes limits via the `limit`, `page`, or `offset` arguments, we
* encourage the `found_orders` and `max_num_pages` properties to also be set.
*
* @since 8.2.0
*
* @param array|null $order_data {
* An array of order data.
* @type int[] $orders Return an array of order IDs data to short-circuit the HPOS query,
* or null to allow HPOS to run its normal query.
* @type int $found_orders The number of orders found.
* @type int $max_num_pages The number of pages.
* }
* @param OrdersTableQuery $query The OrdersTableQuery instance.
* @param string $sql The OrdersTableQuery instance.
*/
$pre_query = apply_filters( 'woocommerce_hpos_pre_query', null, $this, $this->sql );
if ( ! $pre_query || ! isset( $pre_query[0] ) || ! is_array( $pre_query[0] ) ) {
return false;
}
// If the filter set the orders, make sure the others values are set as well and skip running the query.
list( $this->orders, $this->found_orders, $this->max_num_pages ) = $pre_query;
if ( ! is_int( $this->found_orders ) || $this->found_orders < 1 ) {
$this->found_orders = count( $this->orders );
}
if ( ! is_int( $this->max_num_pages ) || $this->max_num_pages < 1 ) {
if ( ! $this->arg_isset( 'limit' ) || ! is_int( $this->args['limit'] ) || $this->args['limit'] < 1 ) {
$this->args['limit'] = 10;
}
$this->max_num_pages = (int) ceil( $this->found_orders / $this->args['limit'] );
}
return true;
}
/**
* Remaps some legacy and `WP_Query` specific query vars to vars available in the customer order table scheme.
*
* @return void
*/
private function maybe_remap_args(): void {
$mapping = array(
// WP_Query legacy.
'post_date' => 'date_created',
'post_date_gmt' => 'date_created_gmt',
'post_modified' => 'date_updated',
'post_modified_gmt' => 'date_updated_gmt',
'post_status' => 'status',
'_date_completed' => 'date_completed',
'_date_paid' => 'date_paid',
'paged' => 'page',
'post_parent' => 'parent_order_id',
'post_parent__in' => 'parent_order_id',
'post_parent__not_in' => 'parent_exclude',
'post__not_in' => 'exclude',
'posts_per_page' => 'limit',
'p' => 'id',
'post__in' => 'id',
'post_type' => 'type',
'fields' => 'return',
'customer_user' => 'customer_id',
'order_currency' => 'currency',
'order_version' => 'woocommerce_version',
'cart_discount' => 'discount_total_amount',
'cart_discount_tax' => 'discount_tax_amount',
'order_shipping' => 'shipping_total_amount',
'order_shipping_tax' => 'shipping_tax_amount',
'order_tax' => 'tax_amount',
// Translate from WC_Order_Query to table structure.
'version' => 'woocommerce_version',
'date_modified' => 'date_updated',
'date_modified_gmt' => 'date_updated_gmt',
'discount_total' => 'discount_total_amount',
'discount_tax' => 'discount_tax_amount',
'shipping_total' => 'shipping_total_amount',
'shipping_tax' => 'shipping_tax_amount',
'cart_tax' => 'tax_amount',
'total' => 'total_amount',
'customer_ip_address' => 'ip_address',
'customer_user_agent' => 'user_agent',
'parent' => 'parent_order_id',
);
foreach ( $mapping as $query_key => $table_field ) {
if ( isset( $this->args[ $query_key ] ) && '' !== $this->args[ $query_key ] ) {
$this->args[ $table_field ] = $this->args[ $query_key ];
unset( $this->args[ $query_key ] );
}
}
// meta_query.
$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
$shortcut_meta_query = array();
foreach ( array( 'key', 'value', 'compare', 'type', 'compare_key', 'type_key' ) as $key ) {
if ( $this->arg_isset( "meta_{$key}" ) ) {
$shortcut_meta_query[ $key ] = $this->args[ "meta_{$key}" ];
}
}
if ( ! empty( $shortcut_meta_query ) ) {
if ( ! empty( $this->args['meta_query'] ) ) {
$this->args['meta_query'] = array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
'relation' => 'AND',
$shortcut_meta_query,
$this->args['meta_query'],
);
} else {
$this->args['meta_query'] = array( $shortcut_meta_query ); // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
}
}
}
/**
* Generates a `WP_Date_Query` compatible query from a given date.
* YYYY-MM-DD queries have 'day' precision for backwards compatibility.
*
* @param mixed $date The date. Can be a {@see \WC_DateTime}, a timestamp or a string.
* @return array An array with keys 'year', 'month', 'day' and possibly 'hour', 'minute' and 'second'.
*/
private function date_to_date_query_arg( $date ): array {
$result = array(
'year' => '',
'month' => '',
'day' => '',
);
$precision = null;
if ( is_numeric( $date ) ) {
$date = new \WC_DateTime( "@{$date}", new \DateTimeZone( 'UTC' ) );
$precision = 'second';
} elseif ( ! is_a( $date, 'WC_DateTime' ) ) {
// For backwards compat (see https://github.com/woocommerce/woocommerce/wiki/wc_get_orders-and-WC_Order_Query#date)
// only YYYY-MM-DD is considered for date values. Timestamps do support second precision.
$date = wc_string_to_datetime( date( 'Y-m-d', strtotime( $date ) ) );
$precision = 'day';
}
$result['year'] = $date->date( 'Y' );
$result['month'] = $date->date( 'm' );
$result['day'] = $date->date( 'd' );
if ( 'second' === $precision ) {
$result['hour'] = $date->date( 'H' );
$result['minute'] = $date->date( 'i' );
$result['second'] = $date->date( 's' );
}
return $result;
}
/**
* Returns UTC-based date query arguments for a combination of local time dates and a date shorthand operator.
*
* @param array $dates_raw Array of dates (in local time) to use in combination with the operator.
* @param string $operator One of the operators supported by date queries (<, <=, =, ..., >, >=).
* @return array Partial date query arg with relevant dates now UTC-based.
*
* @since 8.2.0
*/
private function local_time_to_gmt_date_query( $dates_raw, $operator ) {
$result = array();
// Convert YYYY-MM-DD to UTC timestamp. Per https://github.com/woocommerce/woocommerce/wiki/wc_get_orders-and-WC_Order_Query#date only date is relevant (time is ignored).
foreach ( $dates_raw as &$raw_date ) {
$raw_date = is_numeric( $raw_date ) ? $raw_date : strtotime( get_gmt_from_date( date( 'Y-m-d', strtotime( $raw_date ) ) ) );
}
$date1 = end( $dates_raw );
switch ( $operator ) {
case '>':
$result = array(
'after' => $this->date_to_date_query_arg( $date1 + DAY_IN_SECONDS ),
'inclusive' => true,
);
break;
case '>=':
$result = array(
'after' => $this->date_to_date_query_arg( $date1 ),
'inclusive' => true,
);
break;
case '=':
$result = array(
'relation' => 'AND',
array(
'after' => $this->date_to_date_query_arg( $date1 ),
'inclusive' => true,
),
array(
'before' => $this->date_to_date_query_arg( $date1 + DAY_IN_SECONDS ),
'inclusive' => false,
)
);
break;
case '<=':
$result = array(
'before' => $this->date_to_date_query_arg( $date1 + DAY_IN_SECONDS ),
'inclusive' => false,
);
break;
case '<':
$result = array(
'before' => $this->date_to_date_query_arg( $date1 ),
'inclusive' => false,
);
break;
case '...':
$result = array(
'relation' => 'AND',
$this->local_time_to_gmt_date_query( array( $dates_raw[1] ), '<=' ),
$this->local_time_to_gmt_date_query( array( $dates_raw[0] ), '>=' ),
);
break;
}
if ( ! $result ) {
throw new \Exception( 'Please specify a valid date shorthand operator.' );
}
return $result;
}
/**
* Processes date-related query args and merges the result into 'date_query'.
*
* @return void
* @throws \Exception When date args are invalid.
*/
private function process_date_args(): void {
if ( $this->arg_isset( 'date_query' ) ) {
// Process already passed date queries args.
$this->args['date_query'] = $this->map_gmt_and_post_keys_to_hpos_keys( $this->args['date_query'] );
}
$valid_operators = array( '>', '>=', '=', '<=', '<', '...' );
$date_queries = array();
$local_to_gmt_date_keys = array(
'date_created' => 'date_created_gmt',
'date_updated' => 'date_updated_gmt',
'date_paid' => 'date_paid_gmt',
'date_completed' => 'date_completed_gmt',
);
$gmt_date_keys = array_values( $local_to_gmt_date_keys );
$local_date_keys = array_keys( $local_to_gmt_date_keys );
$valid_date_keys = array_merge( $gmt_date_keys, $local_date_keys );
$date_keys = array_filter( $valid_date_keys, array( $this, 'arg_isset' ) );
foreach ( $date_keys as $date_key ) {
$is_local = in_array( $date_key, $local_date_keys, true );
$date_value = $this->args[ $date_key ];
$operator = '=';
$dates_raw = array();
$dates = array();
if ( is_string( $date_value ) && preg_match( self::REGEX_SHORTHAND_DATES, $date_value, $matches ) ) {
$operator = in_array( $matches[2], $valid_operators, true ) ? $matches[2] : '';
if ( ! empty( $matches[1] ) ) {
$dates_raw[] = $matches[1];
}
$dates_raw[] = $matches[3];
} else {
$dates_raw[] = $date_value;
}
if ( empty( $dates_raw ) || ! $operator || ( '...' === $operator && count( $dates_raw ) < 2 ) ) {
throw new \Exception( 'Invalid date_query' );
}
if ( $is_local ) {
$date_key = $local_to_gmt_date_keys[ $date_key ];
if ( ! is_numeric( $dates_raw[0] ) && ( ! isset( $dates_raw[1] ) || ! is_numeric( $dates_raw[1] ) ) ) {
// Only non-numeric args can be considered local time. Timestamps are assumed to be UTC per https://github.com/woocommerce/woocommerce/wiki/wc_get_orders-and-WC_Order_Query#date.
$date_queries[] = array_merge(
array(
'column' => $date_key,
),
$this->local_time_to_gmt_date_query( $dates_raw, $operator )
);
continue;
}
}
$operator_to_keys = array();
if ( in_array( $operator, array( '>', '>=', '...' ), true ) ) {
$operator_to_keys[] = 'after';
}
if ( in_array( $operator, array( '<', '<=', '...' ), true ) ) {
$operator_to_keys[] = 'before';
}
$dates = array_map( array( $this, 'date_to_date_query_arg' ), $dates_raw );
$date_queries[] = array_merge(
array(
'column' => $date_key,
'inclusive' => ! in_array( $operator, array( '<', '>' ), true ),
),
'=' === $operator
? end( $dates )
: array_combine( $operator_to_keys, $dates )
);
}
// Add top-level date parameters to the date_query.
$tl_query = array();
foreach ( array( 'hour', 'minute', 'second', 'year', 'monthnum', 'week', 'day', 'year' ) as $tl_key ) {
if ( $this->arg_isset( $tl_key ) ) {
$tl_query[ $tl_key ] = $this->args[ $tl_key ];
unset( $this->args[ $tl_key ] );
}
}
if ( $tl_query ) {
$tl_query['column'] = 'date_created_gmt';
$date_queries[] = $tl_query;
}
if ( $date_queries ) {
if ( ! $this->arg_isset( 'date_query' ) ) {
$this->args['date_query'] = array();
}
$this->args['date_query'] = array_merge(
array( 'relation' => 'AND' ),
$date_queries,
$this->args['date_query']
);
}
$this->process_date_query_columns();
}
/**
* Helper function to map posts and gmt based keys to HPOS keys.
*
* @param array $query Date query argument.
*
* @return array|mixed Date query argument with modified keys.
*/
private function map_gmt_and_post_keys_to_hpos_keys( $query ) {
if ( ! is_array( $query ) ) {
return $query;
}
$post_to_hpos_mappings = array(
'post_date' => 'date_created',
'post_date_gmt' => 'date_created_gmt',
'post_modified' => 'date_updated',
'post_modified_gmt' => 'date_updated_gmt',
'_date_completed' => 'date_completed',
'_date_paid' => 'date_paid',
'date_modified' => 'date_updated',
'date_modified_gmt' => 'date_updated_gmt',
);
$local_to_gmt_date_keys = array(
'date_created' => 'date_created_gmt',
'date_updated' => 'date_updated_gmt',
'date_paid' => 'date_paid_gmt',
'date_completed' => 'date_completed_gmt',
);
array_walk(
$query,
function ( &$sub_query ) {
$sub_query = $this->map_gmt_and_post_keys_to_hpos_keys( $sub_query );
}
);
if ( ! isset( $query['column'] ) ) {
return $query;
}
if ( isset( $post_to_hpos_mappings[ $query['column'] ] ) ) {
$query['column'] = $post_to_hpos_mappings[ $query['column'] ];
}
// Convert any local dates to GMT.
if ( isset( $local_to_gmt_date_keys[ $query['column'] ] ) ) {
$query['column'] = $local_to_gmt_date_keys[ $query['column'] ];
$op = isset( $query['after'] ) ? 'after' : 'before';
$date_value_local = $query[ $op ];
$date_value_gmt = wc_string_to_timestamp( get_gmt_from_date( wc_string_to_datetime( $date_value_local ) ) );
$query[ $op ] = $this->date_to_date_query_arg( $date_value_gmt );
}
return $query;
}
/**
* Makes sure all 'date_query' columns are correctly prefixed and their respective tables are being JOIN'ed.
*
* @return void
*/
private function process_date_query_columns() {
global $wpdb;
$legacy_columns = array(
'post_date' => 'date_created_gmt',
'post_date_gmt' => 'date_created_gmt',
'post_modified' => 'date_modified_gmt',
'post_modified_gmt' => 'date_updated_gmt',
);
$table_mapping = array(
'date_created_gmt' => $this->tables['orders'],
'date_updated_gmt' => $this->tables['orders'],
'date_paid_gmt' => $this->tables['operational_data'],
'date_completed_gmt' => $this->tables['operational_data'],
);
if ( empty( $this->args['date_query'] ) ) {
return;
}
array_walk_recursive(
$this->args['date_query'],
function( &$value, $key ) use ( $legacy_columns, $table_mapping, $wpdb ) {
if ( 'column' !== $key ) {
return;
}
// Translate legacy columns from wp_posts if necessary.
$value =
( isset( $legacy_columns[ $value ] ) || isset( $legacy_columns[ "{$wpdb->posts}.{$value}" ] ) )
? $legacy_columns[ $value ]
: $value;
$table = $table_mapping[ $value ] ?? null;
if ( ! $table ) {
return;
}
$value = "{$table}.{$value}";
if ( $table !== $this->tables['orders'] ) {
$this->join( $table, '', '', 'inner', true );
}
}
);
}
/**
* Sanitizes the 'status' query var.
*
* @return void
*/
private function sanitize_status(): void {
// Sanitize status.
$valid_statuses = array_keys( wc_get_order_statuses() );
if ( empty( $this->args['status'] ) || 'any' === $this->args['status'] ) {
$this->args['status'] = $valid_statuses;
} elseif ( 'all' === $this->args['status'] ) {
$this->args['status'] = array();
} else {
$this->args['status'] = is_array( $this->args['status'] ) ? $this->args['status'] : array( $this->args['status'] );
foreach ( $this->args['status'] as &$status ) {
$status = in_array( 'wc-' . $status, $valid_statuses, true ) ? 'wc-' . $status : $status;
}
$this->args['status'] = array_unique( array_filter( $this->args['status'] ) );
}
}
/**
* Parses and sanitizes the 'orderby' query var.
*
* @return void
*/
private function sanitize_order_orderby(): void {
// Allowed keys.
// TODO: rand, meta keys, etc.
$allowed_keys = array( 'ID', 'id', 'type', 'date', 'modified', 'parent' );
// Translate $orderby to a valid field.
$mapping = array(
'ID' => "{$this->tables['orders']}.id",
'id' => "{$this->tables['orders']}.id",
'type' => "{$this->tables['orders']}.type",
'date' => "{$this->tables['orders']}.date_created_gmt",
'date_created' => "{$this->tables['orders']}.date_created_gmt",
'modified' => "{$this->tables['orders']}.date_updated_gmt",
'date_modified' => "{$this->tables['orders']}.date_updated_gmt",
'parent' => "{$this->tables['orders']}.parent_order_id",
'total' => "{$this->tables['orders']}.total_amount",
'order_total' => "{$this->tables['orders']}.total_amount",
);
$order = $this->args['order'] ?? '';
$orderby = $this->args['orderby'] ?? '';
if ( 'none' === $orderby ) {
return;
}
// No need to sanitize, will be processed in calling function.
if ( 'include' === $orderby || 'post__in' === $orderby ) {
return;
}
if ( is_string( $orderby ) ) {
$orderby_fields = array_map( 'trim', explode( ' ', $orderby ) );
$orderby = array();
foreach ( $orderby_fields as $field ) {
$orderby[ $field ] = $order;
}
}
$allowed_orderby = array_merge(
array_keys( $mapping ),
array_values( $mapping ),
$this->meta_query ? $this->meta_query->get_orderby_keys() : array()
);
$this->args['orderby'] = array();
foreach ( $orderby as $order_key => $order ) {
if ( ! in_array( $order_key, $allowed_orderby, true ) ) {
continue;
}
if ( isset( $mapping[ $order_key ] ) ) {
$order_key = $mapping[ $order_key ];
}
$this->args['orderby'][ $order_key ] = $this->sanitize_order( $order );
}
}
/**
* Makes sure the order in an ORDER BY statement is either 'ASC' o 'DESC'.
*
* @param string $order The unsanitized order.
* @return string The sanitized order.
*/
private function sanitize_order( string $order ): string {
$order = strtoupper( $order );
return in_array( $order, array( 'ASC', 'DESC' ), true ) ? $order : 'DESC';
}
/**
* Builds the final SQL query to be run.
*
* @return void
*/
private function build_query(): void {
$this->maybe_remap_args();
// Field queries.
if ( ! empty( $this->args['field_query'] ) ) {
$this->field_query = new OrdersTableFieldQuery( $this );
$sql = $this->field_query->get_sql_clauses();
$this->join = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
$this->where = $sql['where'] ? array_merge( $this->where, $sql['where'] ) : $this->where;
}
// Build query.
$this->process_date_args();
$this->process_orders_table_query_args();
$this->process_operational_data_table_query_args();
$this->process_addresses_table_query_args();
// Search queries.
if ( ! empty( $this->args['s'] ) ) {
$this->search_query = new OrdersTableSearchQuery( $this );
$sql = $this->search_query->get_sql_clauses();
$this->join = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
$this->where = $sql['where'] ? array_merge( $this->where, $sql['where'] ) : $this->where;
}
// Meta queries.
if ( ! empty( $this->args['meta_query'] ) ) {
$this->meta_query = new OrdersTableMetaQuery( $this );
$sql = $this->meta_query->get_sql_clauses();
$this->join = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
$this->where = $sql['where'] ? array_merge( $this->where, array( $sql['where'] ) ) : $this->where;
}
// Date queries.
if ( ! empty( $this->args['date_query'] ) ) {
$this->date_query = new \WP_Date_Query( $this->args['date_query'], "{$this->tables['orders']}.date_created_gmt" );
$this->where[] = substr( trim( $this->date_query->get_sql() ), 3 ); // WP_Date_Query includes "AND".
}
$this->process_orderby();
$this->process_limit();
$orders_table = $this->tables['orders'];
// 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.
$this->groupby[] = "{$this->tables['orders']}.id";
$this->fields = "{$orders_table}.id";
$fields = $this->fields;
// JOIN.
$join = implode( ' ', array_unique( array_filter( array_map( 'trim', $this->join ) ) ) );
// WHERE.
$where = '1=1';
foreach ( $this->where as $_where ) {
$where .= " AND ({$_where})";
}
// ORDER BY.
$orderby = $this->orderby ? implode( ', ', $this->orderby ) : '';
// LIMITS.
$limits = '';
if ( ! empty( $this->limits ) && count( $this->limits ) === 2 ) {
list( $offset, $row_count ) = $this->limits;
$row_count = -1 === $row_count ? self::MYSQL_MAX_UNSIGNED_BIGINT : (int) $row_count;
$limits = 'LIMIT ' . (int) $offset . ', ' . $row_count;
}
// GROUP BY.
$groupby = $this->groupby ? implode( ', ', (array) $this->groupby ) : '';
$pieces = compact( 'fields', 'join', 'where', 'groupby', 'orderby', 'limits' );
if ( ! $this->suppress_filters ) {
/**
* Filters all query clauses at once.
* Covers the fields (SELECT), JOIN, WHERE, GROUP BY, ORDER BY, and LIMIT clauses.
*
* @since 7.9.0
*
* @param string[] $clauses {
* Associative array of the clauses for the query.
*
* @type string $fields The SELECT clause of the query.
* @type string $join The JOIN clause of the query.
* @type string $where The WHERE clause of the query.
* @type string $groupby The GROUP BY clause of the query.
* @type string $orderby The ORDER BY clause of the query.
* @type string $limits The LIMIT clause of the query.
* }
* @param OrdersTableQuery $query The OrdersTableQuery instance (passed by reference).
* @param array $args Query args.
*/
$clauses = (array) apply_filters_ref_array( 'woocommerce_orders_table_query_clauses', array( $pieces, &$this, $this->args ) );
$fields = $clauses['fields'] ?? '';
$join = $clauses['join'] ?? '';
$where = $clauses['where'] ?? '';
$groupby = $clauses['groupby'] ?? '';
$orderby = $clauses['orderby'] ?? '';
$limits = $clauses['limits'] ?? '';
}
$groupby = $groupby ? ( 'GROUP BY ' . $groupby ) : '';
$orderby = $orderby ? ( 'ORDER BY ' . $orderby ) : '';
$this->sql = "SELECT $fields FROM $orders_table $join WHERE $where $groupby $orderby $limits";
if ( ! $this->suppress_filters ) {
/**
* Filters the completed SQL query.
*
* @since 7.9.0
*
* @param string $sql The complete SQL query.
* @param OrdersTableQuery $query The OrdersTableQuery instance (passed by reference).
* @param array $args Query args.
*/
$this->sql = apply_filters_ref_array( 'woocommerce_orders_table_query_sql', array( $this->sql, &$this, $this->args ) );
}
$this->build_count_query( $fields, $join, $where, $groupby );
}
/**
* Build SQL query for counting total number of results.
*
* @param string $fields Prepared fields for SELECT clause.
* @param string $join Prepared JOIN clause.
* @param string $where Prepared WHERE clause.
* @param string $groupby Prepared GROUP BY clause.
*/
private function build_count_query( $fields, $join, $where, $groupby ) {
if ( ! isset( $this->sql ) || '' === $this->sql ) {
wc_doing_it_wrong( __FUNCTION__, 'Count query can only be build after main query is built.', '7.3.0' );
}
$orders_table = $this->tables['orders'];
$this->count_sql = "SELECT COUNT(DISTINCT $fields) FROM $orders_table $join WHERE $where";
if ( ! $this->suppress_filters ) {
/**
* Filters the count SQL query.
*
* @since 8.6.0
*
* @param string $sql The count SQL query.
* @param OrdersTableQuery $query The OrdersTableQuery instance (passed by reference).
* @param array $args Query args.
* @param string $fields Prepared fields for SELECT clause.
* @param string $join Prepared JOIN clause.
* @param string $where Prepared WHERE clause.
* @param string $groupby Prepared GROUP BY clause.
*/
$this->count_sql = apply_filters_ref_array( 'woocommerce_orders_table_query_count_sql', array( $this->count_sql, &$this, $this->args, $fields, $join, $where, $groupby ) );
}
}
/**
* Returns the table alias for a given table mapping.
*
* @param string $mapping_id The mapping name (e.g. 'orders' or 'operational_data').
* @return string Table alias.
*
* @since 7.0.0
*/
public function get_core_mapping_alias( string $mapping_id ): string {
return in_array( $mapping_id, array( 'billing_address', 'shipping_address' ), true )
? $mapping_id
: $this->tables[ $mapping_id ];
}
/**
* Returns an SQL JOIN clause that can be used to join the main orders table with another order table.
*
* @param string $mapping_id The mapping name (e.g. 'orders' or 'operational_data').
* @return string The JOIN clause.
*
* @since 7.0.0
*/
public function get_core_mapping_join( string $mapping_id ): string {
global $wpdb;
if ( 'orders' === $mapping_id ) {
return '';
}
$is_address_mapping = in_array( $mapping_id, array( 'billing_address', 'shipping_address' ), true );
$alias = $this->get_core_mapping_alias( $mapping_id );
$table = $is_address_mapping ? $this->tables['addresses'] : $this->tables[ $mapping_id ];
$join = '';
$join_on = '';
$join .= "INNER JOIN `{$table}`" . ( $alias !== $table ? " AS `{$alias}`" : '' );
if ( isset( $this->mappings[ $mapping_id ]['order_id'] ) ) {
$join_on .= "`{$this->tables['orders']}`.id = `{$alias}`.order_id";
}
if ( $is_address_mapping ) {
$join_on .= $wpdb->prepare( " AND `{$alias}`.address_type = %s", substr( $mapping_id, 0, -8 ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
}
return $join . ( $join_on ? " ON ( {$join_on} )" : '' );
}
/**
* JOINs the main orders table with another table.
*
* @param string $table Table name (including prefix).
* @param string $alias Table alias to use. Defaults to $table.
* @param string $on ON clause. Defaults to "wc_orders.id = {$alias}.order_id".
* @param string $join_type JOIN type: LEFT, RIGHT or INNER.
* @param boolean $alias_once If TRUE, table won't be JOIN'ed again if already JOIN'ed.
* @return void
* @throws \Exception When an error occurs, such as trying to re-use an alias with $alias_once = FALSE.
*/
private function join( string $table, string $alias = '', string $on = '', string $join_type = 'inner', bool $alias_once = false ) {
$alias = empty( $alias ) ? $table : $alias;
$join_type = strtoupper( trim( $join_type ) );
if ( $this->tables['orders'] === $alias ) {
// translators: %s is a table name.
throw new \Exception( sprintf( __( '%s can not be used as a table alias in OrdersTableQuery', 'woocommerce' ), $alias ) );
}
if ( empty( $on ) ) {
if ( $this->tables['orders'] === $table ) {
$on = "`{$this->tables['orders']}`.id = `{$alias}`.id";
} else {
$on = "`{$this->tables['orders']}`.id = `{$alias}`.order_id";
}
}
if ( isset( $this->join[ $alias ] ) ) {
if ( ! $alias_once ) {
// translators: %s is a table name.
throw new \Exception( sprintf( __( 'Can not re-use table alias "%s" in OrdersTableQuery.', 'woocommerce' ), $alias ) );
}
return;
}
if ( '' === $join_type || ! in_array( $join_type, array( 'LEFT', 'RIGHT', 'INNER' ), true ) ) {
$join_type = 'INNER';
}
$sql_join = '';
$sql_join .= "{$join_type} JOIN `{$table}` ";
$sql_join .= ( $alias !== $table ) ? "AS `{$alias}` " : '';
$sql_join .= "ON ( {$on} )";
$this->join[ $alias ] = $sql_join;
}
/**
* Generates a properly escaped and sanitized WHERE condition for a given field.
*
* @param string $table The table the field belongs to.
* @param string $field The field or column name.
* @param string $operator The operator to use in the condition. Defaults to '=' or 'IN' depending on $value.
* @param mixed $value The value.
* @param string $type The column type as specified in {@see OrdersTableDataStore} column mappings.
* @return string The resulting WHERE condition.
*/
public function where( string $table, string $field, string $operator, $value, string $type ): string {
global $wpdb;
$db_util = wc_get_container()->get( DatabaseUtil::class );
$operator = strtoupper( '' !== $operator ? $operator : '=' );
try {
$format = $db_util->get_wpdb_format_for_type( $type );
} catch ( \Exception $e ) {
$format = '%s';
}
// = and != can be shorthands for IN and NOT in for array values.
if ( is_array( $value ) && '=' === $operator ) {
$operator = 'IN';
} elseif ( is_array( $value ) && '!=' === $operator ) {
$operator = 'NOT IN';
}
if ( ! in_array( $operator, array( '=', '!=', 'IN', 'NOT IN' ), true ) ) {
return false;
}
if ( is_array( $value ) ) {
$value = array_map( array( $db_util, 'format_object_value_for_db' ), $value, array_fill( 0, count( $value ), $type ) );
} else {
$value = $db_util->format_object_value_for_db( $value, $type );
}
if ( is_array( $value ) ) {
$placeholder = array_fill( 0, count( $value ), $format );
$placeholder = '(' . implode( ',', $placeholder ) . ')';
} else {
$placeholder = $format;
}
$sql = $wpdb->prepare( "{$table}.{$field} {$operator} {$placeholder}", $value ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
return $sql;
}
/**
* Processes fields related to the orders table.
*
* @return void
*/
private function process_orders_table_query_args(): void {
$this->sanitize_status();
$fields = array_filter(
array(
'id',
'status',
'type',
'currency',
'tax_amount',
'customer_id',
'billing_email',
'total_amount',
'parent_order_id',
'payment_method',
'payment_method_title',
'transaction_id',
'ip_address',
'user_agent',
),
array( $this, 'arg_isset' )
);
foreach ( $fields as $arg_key ) {
$this->where[] = $this->where( $this->tables['orders'], $arg_key, '=', $this->args[ $arg_key ], $this->mappings['orders'][ $arg_key ]['type'] );
}
if ( $this->arg_isset( 'parent_exclude' ) ) {
$this->where[] = $this->where( $this->tables['orders'], 'parent_order_id', '!=', $this->args['parent_exclude'], 'int' );
}
if ( $this->arg_isset( 'exclude' ) ) {
$this->where[] = $this->where( $this->tables['orders'], 'id', '!=', $this->args['exclude'], 'int' );
}
// 'customer' is a very special field.
if ( $this->arg_isset( 'customer' ) ) {
$customer_query = $this->generate_customer_query( $this->args['customer'] );
if ( $customer_query ) {
$this->where[] = $customer_query;
}
}
}
/**
* Generate SQL conditions for the 'customer' query.
*
* @param array $values List of customer ids or emails.
* @param string $relation 'OR' or 'AND' relation used to build the customer query.
* @return string SQL to be used in a WHERE clause.
*/
private function generate_customer_query( $values, string $relation = 'OR' ): string {
$values = is_array( $values ) ? $values : array( $values );
$ids = array();
$emails = array();
$pieces = array();
foreach ( $values as $value ) {
if ( is_array( $value ) ) {
$sql = $this->generate_customer_query( $value, 'AND' );
$pieces[] = $sql ? '(' . $sql . ')' : '';
} elseif ( is_numeric( $value ) ) {
$ids[] = absint( $value );
} elseif ( is_string( $value ) && is_email( $value ) ) {
$emails[] = sanitize_email( $value );
} else {
// Invalid query.
$pieces[] = '1=0';
}
}
if ( $ids ) {
$pieces[] = $this->where( $this->tables['orders'], 'customer_id', '=', $ids, 'int' );
}
if ( $emails ) {
$pieces[] = $this->where( $this->tables['orders'], 'billing_email', '=', $emails, 'string' );
}
return $pieces ? implode( " $relation ", $pieces ) : '';
}
/**
* Processes fields related to the operational data table.
*
* @return void
*/
private function process_operational_data_table_query_args(): void {
$fields = array_filter(
array(
'created_via',
'woocommerce_version',
'prices_include_tax',
'order_key',
'discount_total_amount',
'discount_tax_amount',
'shipping_total_amount',
'shipping_tax_amount',
),
array( $this, 'arg_isset' )
);
if ( ! $fields ) {
return;
}
$this->join(
$this->tables['operational_data'],
'',
'',
'inner',
true
);
foreach ( $fields as $arg_key ) {
$this->where[] = $this->where( $this->tables['operational_data'], $arg_key, '=', $this->args[ $arg_key ], $this->mappings['operational_data'][ $arg_key ]['type'] );
}
}
/**
* Processes fields related to the addresses table.
*
* @return void
*/
private function process_addresses_table_query_args(): void {
global $wpdb;
foreach ( array( 'billing', 'shipping' ) as $address_type ) {
$fields = array_filter(
array(
$address_type . '_first_name',
$address_type . '_last_name',
$address_type . '_company',
$address_type . '_address_1',
$address_type . '_address_2',
$address_type . '_city',
$address_type . '_state',
$address_type . '_postcode',
$address_type . '_country',
$address_type . '_phone',
),
array( $this, 'arg_isset' )
);
if ( ! $fields ) {
continue;
}
$this->join(
$this->tables['addresses'],
$address_type,
$wpdb->prepare( "{$this->tables['orders']}.id = {$address_type}.order_id AND {$address_type}.address_type = %s", $address_type ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
'inner',
false
);
foreach ( $fields as $arg_key ) {
$column_name = str_replace( "{$address_type}_", '', $arg_key );
$this->where[] = $this->where(
$address_type,
$column_name,
'=',
$this->args[ $arg_key ],
$this->mappings[ "{$address_type}_address" ][ $column_name ]['type']
);
}
}
}
/**
* Generates the ORDER BY clause.
*
* @return void
*/
private function process_orderby(): void {
// 'order' and 'orderby' vars.
$this->args['order'] = $this->sanitize_order( $this->args['order'] ?? '' );
$this->sanitize_order_orderby();
$orderby = $this->args['orderby'];
if ( 'none' === $orderby ) {
$this->orderby = '';
return;
}
if ( 'include' === $orderby || 'post__in' === $orderby ) {
$ids = $this->args['id'] ?? $this->args['includes'];
if ( empty( $ids ) ) {
return;
}
$ids = array_map( 'absint', $ids );
$this->orderby = array( "FIELD( {$this->tables['orders']}.id, " . implode( ',', $ids ) . ' )' );
return;
}
$meta_orderby_keys = $this->meta_query ? $this->meta_query->get_orderby_keys() : array();
$orderby_array = array();
foreach ( $this->args['orderby'] as $_orderby => $order ) {
if ( in_array( $_orderby, $meta_orderby_keys, true ) ) {
$_orderby = $this->meta_query->get_orderby_clause_for_key( $_orderby );
}
$orderby_array[] = "{$_orderby} {$order}";
}
$this->orderby = $orderby_array;
}
/**
* Generates the limits to be used in the LIMIT clause.
*
* @return void
*/
private function process_limit(): void {
$row_count = ( $this->arg_isset( 'limit' ) ? (int) $this->args['limit'] : false );
$page = ( $this->arg_isset( 'page' ) ? absint( $this->args['page'] ) : 1 );
$offset = ( $this->arg_isset( 'offset' ) ? absint( $this->args['offset'] ) : false );
// Bool false indicates no limit was specified; less than -1 means an invalid value was passed (such as -3).
if ( false === $row_count || $row_count < -1 ) {
return;
}
if ( false === $offset && $row_count > -1 ) {
$offset = (int) ( ( $page - 1 ) * $row_count );
}
$this->limits = array( $offset, $row_count );
}
/**
* Checks if a query var is set (i.e. not one of the "skipped values").
*
* @param string $arg_key Query var.
* @return bool TRUE if query var is set.
*/
public function arg_isset( string $arg_key ): bool {
return ( isset( $this->args[ $arg_key ] ) && ! in_array( $this->args[ $arg_key ], self::SKIPPED_VALUES, true ) );
}
/**
* Runs the SQL query.
*
* @return void
*/
private function run_query(): void {
global $wpdb;
// Run query.
$this->orders = array_map( 'absint', $wpdb->get_col( $this->sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
// Set max_num_pages and found_orders if necessary.
if ( ( $this->arg_isset( 'no_found_rows' ) && ! $this->args['no_found_rows'] ) || empty( $this->orders ) ) {
return;
}
if ( $this->limits ) {
$this->found_orders = absint( $wpdb->get_var( $this->count_sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$this->max_num_pages = (int) ceil( $this->found_orders / $this->args['limit'] );
} else {
$this->found_orders = count( $this->orders );
}
}
/**
* Make some private available for backwards compatibility.
*
* @param string $name Property to get.
* @return mixed
*/
public function __get( string $name ) {
switch ( $name ) {
case 'found_orders':
case 'found_posts':
return $this->found_orders;
case 'max_num_pages':
return $this->max_num_pages;
case 'posts':
case 'orders':
return $this->orders;
case 'request':
return $this->sql;
default:
break;
}
}
/**
* Returns the value of one of the query arguments.
*
* @param string $arg_name Query var.
* @return mixed
*/
public function get( string $arg_name ) {
return $this->args[ $arg_name ] ?? null;
}
/**
* Returns the name of one of the OrdersTableDatastore tables.
*
* @param string $table_id Table identifier. One of 'orders', 'operational_data', 'addresses', 'meta'.
* @return string The prefixed table name.
* @throws \Exception When table ID is not found.
*/
public function get_table_name( string $table_id = '' ): string {
if ( ! isset( $this->tables[ $table_id ] ) ) {
// Translators: %s is a table identifier.
throw new \Exception( sprintf( __( 'Invalid table id: %s.', 'woocommerce' ), $table_id ) );
}
return $this->tables[ $table_id ];
}
/**
* Finds table and mapping information about a field or column.
*
* @param string $field Field to look for in `<mapping|field_name>.<column|field_name>` format or just `<field_name>`.
* @return false|array {
* @type string $table Full table name where the field is located.
* @type string $mapping_id Unprefixed table or mapping name.
* @type string $field_name Name of the corresponding order field.
* @type string $column Column in $table that corresponds to the field.
* @type string $type Field type.
* }
*/
public function get_field_mapping_info( $field ) {
global $wpdb;
$result = array(
'table' => '',
'mapping_id' => '',
'field_name' => '',
'column' => '',
'column_type' => '',
);
$mappings_to_search = array();
if ( false !== strstr( $field, '.' ) ) {
list( $mapping_or_table, $field_name_or_col ) = explode( '.', $field );
$mapping_or_table = substr( $mapping_or_table, 0, strlen( $wpdb->prefix ) ) === $wpdb->prefix ? substr( $mapping_or_table, strlen( $wpdb->prefix ) ) : $mapping_or_table;
$mapping_or_table = 'wc_' === substr( $mapping_or_table, 0, 3 ) ? substr( $mapping_or_table, 3 ) : $mapping_or_table;
if ( isset( $this->mappings[ $mapping_or_table ] ) ) {
if ( isset( $this->mappings[ $mapping_or_table ][ $field_name_or_col ] ) ) {
$result['mapping_id'] = $mapping_or_table;
$result['column'] = $field_name_or_col;
} else {
$mappings_to_search = array( $mapping_or_table );
}
}
} else {
$field_name_or_col = $field;
$mappings_to_search = array_keys( $this->mappings );
}
foreach ( $mappings_to_search as $mapping_id ) {
foreach ( $this->mappings[ $mapping_id ] as $column_name => $column_data ) {
if ( isset( $column_data['name'] ) && $column_data['name'] === $field_name_or_col ) {
$result['mapping_id'] = $mapping_id;
$result['column'] = $column_name;
break 2;
}
}
}
if ( ! $result['mapping_id'] || ! $result['column'] ) {
return false;
}
$field_info = $this->mappings[ $result['mapping_id'] ][ $result['column'] ];
$result['field_name'] = $field_info['name'];
$result['column_type'] = $field_info['type'];
$result['table'] = ( in_array( $result['mapping_id'], array( 'billing_address', 'shipping_address' ), true ) )
? $this->tables['addresses']
: $this->tables[ $result['mapping_id'] ];
return $result;
}
}