ActionScheduler_DBStore::get_query_actions_sql() │ protected │ WC .

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

ActionScheduler_DBStore::get_query_actions_sql() ActionScheduler DBStore::get query actions sql code ^{WC 7.3.0}

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,
			'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;
	$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 ( $query['hook'] ) {
		$sql         .= ' AND a.hook=%s';
		$sql_params[] = $query['hook'];
	}
	if ( ! is_null( $query['args'] ) ) {
		$sql         .= ' AND a.args=%s';
		$sql_params[] = $this->get_args_for_query( $query['args'] );
	}

	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;
}