ActionScheduler_DBStore::get_query_actions_sql() │ protected │ WC 3.3.0

Returns the SQL statement to query (or count) actions.

Method of the class: ActionScheduler_DBStore{}

No Hooks.

Return

String. SQL statement already properly escaped.

Usage

// protected - for code of main (parent) or child class
$result = $this->get_query_actions_sql( $query, $select_or_count );
$query(array) (required)
Filtering options.
$select_or_count(string)
Whether the SQL should select and return the IDs or just the row count.
Default: 'select'

Changelog

Since 3.3.0 Introduced.
Since 3.3.0 $query['status'] accepts array of statuses instead of a single status.

ActionScheduler_DBStore::get_query_actions_sql() code WC 9.5.1

woocommerce/packages/action-scheduler/classes/data-stores/ActionScheduler_DBStore.php 
protected function get_query_actions_sql( array $query, $select_or_count = 'select' ) {

	if ( ! in_array( $select_or_count, array( 'select', 'count' ), true ) ) {
		throw new InvalidArgumentException( __( 'Invalid value for select or count parameter. Cannot query actions.', 'woocommerce' ) );
	}

	$query = wp_parse_args( $query, array(
		'hook'                  => '',
		'args'                  => null,
		'partial_args_matching' => 'off', // can be 'like' or 'json'.
		'date'                  => null,
		'date_compare'          => '<=',
		'modified'              => null,
		'modified_compare'      => '<=',
		'group'                 => '',
		'status'                => '',
		'claimed'               => null,
		'per_page'              => 5,
		'offset'                => 0,
		'orderby'               => 'date',
		'order'                 => 'ASC',
	 ) );

	/** @var \wpdb $wpdb */
	global $wpdb;

	$db_server_info = is_callable( array( $wpdb, 'db_server_info' ) ) ? $wpdb->db_server_info() : $wpdb->db_version();
	if ( false !== strpos( $db_server_info, 'MariaDB' ) ) {
		$supports_json = version_compare(
			PHP_VERSION_ID >= 80016 ? $wpdb->db_version() : preg_replace( '/[^0-9.].*/', '', str_replace( '5.5.5-', '', $db_server_info ) ),
			'10.2',
			'>='
		);
	} else {
		$supports_json = version_compare( $wpdb->db_version(), '5.7', '>=' );
	}

	$sql        = ( 'count' === $select_or_count ) ? 'SELECT count(a.action_id)' : 'SELECT a.action_id';
	$sql        .= " FROM {$wpdb->actionscheduler_actions} a";
	$sql_params = array();

	if ( ! empty( $query['group'] ) || 'group' === $query['orderby'] ) {
		$sql .= " LEFT JOIN {$wpdb->actionscheduler_groups} g ON g.group_id=a.group_id";
	}

	$sql .= ' WHERE 1=1';

	if ( ! empty( $query['group'] ) ) {
		$sql          .= ' AND g.slug=%s';
		$sql_params[] = $query['group'];
	}

	if ( ! empty( $query['hook'] ) ) {
		$sql          .= ' AND a.hook=%s';
		$sql_params[] = $query['hook'];
	}

	if ( ! is_null( $query['args'] ) ) {
		switch ( $query['partial_args_matching'] ) {
			case 'json':
				if ( ! $supports_json ) {
					throw new \RuntimeException( __( 'JSON partial matching not supported in your environment. Please check your MySQL/MariaDB version.', 'woocommerce' ) );
				}
				$supported_types = array(
					'integer' => '%d',
					'boolean' => '%s',
					'double'  => '%f',
					'string'  => '%s',
				);
				foreach ( $query['args'] as $key => $value ) {
					$value_type = gettype( $value );
					if ( 'boolean' === $value_type ) {
						$value = $value ? 'true' : 'false';
					}
					$placeholder = isset( $supported_types[ $value_type ] ) ? $supported_types[ $value_type ] : false;
					if ( ! $placeholder ) {
						throw new \RuntimeException( sprintf(
							/* translators: %s: provided value type */
							__( 'The value type for the JSON partial matching is not supported. Must be either integer, boolean, double or string. %s type provided.', 'woocommerce' ),
							$value_type
						) );
					}
					$sql          .= ' AND JSON_EXTRACT(a.args, %s)=' . $placeholder;
					$sql_params[] = '$.' . $key;
					$sql_params[] = $value;
				}
				break;
			case 'like':
				foreach ( $query['args'] as $key => $value ) {
					$sql          .= ' AND a.args LIKE %s';
					$json_partial = $wpdb->esc_like( trim( wp_json_encode( array( $key => $value ) ), '{}' ) );
					$sql_params[] = "%{$json_partial}%";
				}
				break;
			case 'off':
				$sql          .= ' AND a.args=%s';
				$sql_params[] = $this->get_args_for_query( $query['args'] );
				break;
			default:
				throw new \RuntimeException( __( 'Unknown partial args matching value.', 'woocommerce' ) );
		}
	}

	if ( $query['status'] ) {
		$statuses     = (array) $query['status'];
		$placeholders = array_fill( 0, count( $statuses ), '%s' );
		$sql         .= ' AND a.status IN (' . join( ', ', $placeholders ) . ')';
		$sql_params   = array_merge( $sql_params, array_values( $statuses ) );
	}

	if ( $query['date'] instanceof \DateTime ) {
		$date = clone $query['date'];
		$date->setTimezone( new \DateTimeZone( 'UTC' ) );
		$date_string  = $date->format( 'Y-m-d H:i:s' );
		$comparator   = $this->validate_sql_comparator( $query['date_compare'] );
		$sql         .= " AND a.scheduled_date_gmt $comparator %s";
		$sql_params[] = $date_string;
	}

	if ( $query['modified'] instanceof \DateTime ) {
		$modified = clone $query['modified'];
		$modified->setTimezone( new \DateTimeZone( 'UTC' ) );
		$date_string  = $modified->format( 'Y-m-d H:i:s' );
		$comparator   = $this->validate_sql_comparator( $query['modified_compare'] );
		$sql         .= " AND a.last_attempt_gmt $comparator %s";
		$sql_params[] = $date_string;
	}

	if ( true === $query['claimed'] ) {
		$sql .= ' AND a.claim_id != 0';
	} elseif ( false === $query['claimed'] ) {
		$sql .= ' AND a.claim_id = 0';
	} elseif ( ! is_null( $query['claimed'] ) ) {
		$sql         .= ' AND a.claim_id = %d';
		$sql_params[] = $query['claimed'];
	}

	if ( ! empty( $query['search'] ) ) {
		$sql .= ' AND (a.hook LIKE %s OR (a.extended_args IS NULL AND a.args LIKE %s) OR a.extended_args LIKE %s';
		for ( $i = 0; $i < 3; $i++ ) {
			$sql_params[] = sprintf( '%%%s%%', $query['search'] );
		}

		$search_claim_id = (int) $query['search'];
		if ( $search_claim_id ) {
			$sql         .= ' OR a.claim_id = %d';
			$sql_params[] = $search_claim_id;
		}

		$sql .= ')';
	}

	if ( 'select' === $select_or_count ) {
		if ( 'ASC' === strtoupper( $query['order'] ) ) {
			$order = 'ASC';
		} else {
			$order = 'DESC';
		}
		switch ( $query['orderby'] ) {
			case 'hook':
				$sql .= " ORDER BY a.hook $order";
				break;
			case 'group':
				$sql .= " ORDER BY g.slug $order";
				break;
			case 'modified':
				$sql .= " ORDER BY a.last_attempt_gmt $order";
				break;
			case 'none':
				break;
			case 'action_id':
				$sql .= " ORDER BY a.action_id $order";
				break;
			case 'date':
			default:
				$sql .= " ORDER BY a.scheduled_date_gmt $order";
				break;
		}

		if ( $query['per_page'] > 0 ) {
			$sql         .= ' LIMIT %d, %d';
			$sql_params[] = $query['offset'];
			$sql_params[] = $query['per_page'];
		}
	}

	if ( ! empty( $sql_params ) ) {
		$sql = $wpdb->prepare( $sql, $sql_params ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	return $sql;
}