ActionScheduler_wpPostStore::get_query_actions_sql() │ protected │ WC 1.0

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

Method of the class: ActionScheduler_wpPostStore{}

No Hooks.

Return

String. SQL statement. The returned SQL is 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'

ActionScheduler_wpPostStore::get_query_actions_sql() ActionScheduler wpPostStore::get query actions sql code ^{WC 9.4.2}

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 schedule. Cannot save action.', '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',
			'search'           => '',
		)
	);

	/**
	 * Global wpdb object.
	 *
	 * @var wpdb $wpdb
	 */
	global $wpdb;
	$sql        = ( 'count' === $select_or_count ) ? 'SELECT count(p.ID)' : 'SELECT p.ID ';
	$sql       .= "FROM {$wpdb->posts} p";
	$sql_params = array();
	if ( empty( $query['group'] ) && 'group' === $query['orderby'] ) {
		$sql .= " LEFT JOIN {$wpdb->term_relationships} tr ON tr.object_id=p.ID";
		$sql .= " LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id=tt.term_taxonomy_id";
		$sql .= " LEFT JOIN {$wpdb->terms} t ON tt.term_id=t.term_id";
	} elseif ( ! empty( $query['group'] ) ) {
		$sql         .= " INNER JOIN {$wpdb->term_relationships} tr ON tr.object_id=p.ID";
		$sql         .= " INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id=tt.term_taxonomy_id";
		$sql         .= " INNER JOIN {$wpdb->terms} t ON tt.term_id=t.term_id";
		$sql         .= ' AND t.slug=%s';
		$sql_params[] = $query['group'];
	}
	$sql         .= ' WHERE post_type=%s';
	$sql_params[] = self::POST_TYPE;
	if ( $query['hook'] ) {
		$sql         .= ' AND p.post_title=%s';
		$sql_params[] = $query['hook'];
	}
	if ( ! is_null( $query['args'] ) ) {
		$sql         .= ' AND p.post_content=%s';
		$sql_params[] = wp_json_encode( $query['args'] );
	}

	if ( $query['status'] ) {
		$post_statuses = array_map( array( $this, 'get_post_status_by_action_status' ), (array) $query['status'] );
		$placeholders  = array_fill( 0, count( $post_statuses ), '%s' );
		$sql          .= ' AND p.post_status IN (' . join( ', ', $placeholders ) . ')';
		$sql_params    = array_merge( $sql_params, array_values( $post_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 p.post_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 p.post_modified_gmt $comparator %s";
		$sql_params[] = $date_string;
	}

	if ( true === $query['claimed'] ) {
		$sql .= " AND p.post_password != ''";
	} elseif ( false === $query['claimed'] ) {
		$sql .= " AND p.post_password = ''";
	} elseif ( ! is_null( $query['claimed'] ) ) {
		$sql         .= ' AND p.post_password = %s';
		$sql_params[] = $query['claimed'];
	}

	if ( ! empty( $query['search'] ) ) {
		$sql .= ' AND (p.post_title LIKE %s OR p.post_content LIKE %s OR p.post_password LIKE %s)';
		for ( $i = 0; $i < 3; $i++ ) {
			$sql_params[] = sprintf( '%%%s%%', $query['search'] );
		}
	}

	if ( 'select' === $select_or_count ) {
		switch ( $query['orderby'] ) {
			case 'hook':
				$orderby = 'p.post_title';
				break;
			case 'group':
				$orderby = 't.name';
				break;
			case 'status':
				$orderby = 'p.post_status';
				break;
			case 'modified':
				$orderby = 'p.post_modified';
				break;
			case 'claim_id':
				$orderby = 'p.post_password';
				break;
			case 'schedule':
			case 'date':
			default:
				$orderby = 'p.post_date_gmt';
				break;
		}
		if ( 'ASC' === strtoupper( $query['order'] ) ) {
			$order = 'ASC';
		} else {
			$order = 'DESC';
		}
		$sql .= " ORDER BY $orderby $order";
		if ( $query['per_page'] > 0 ) {
			$sql         .= ' LIMIT %d, %d';
			$sql_params[] = $query['offset'];
			$sql_params[] = $query['per_page'];
		}
	}

	return $wpdb->prepare( $sql, $sql_params ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
}