page_size = max( 1, (int) $page_size );
$this->set_columns( $columns );
}
/**
* Constructs the query and executes it, returning an array of objects containing the columns this object was
* constructed with. Every object will always contain the ID column.
*
* @param int $page Paginated page to retrieve.
*
* @return array An array of associative arrays containing the keys as requested in the constructor.
*/
public function get_data( $page = 1 ) {
global $wpdb;
if ( $this->columns === [] ) {
return [];
}
$post_types = WPSEO_Post_Type::get_accessible_post_types();
if ( empty( $post_types ) ) {
return [];
}
// Pages have a starting index of 1, we need to convert to a 0 based offset.
$offset_multiplier = max( 0, ( $page - 1 ) );
$replacements = [];
$replacements[] = $wpdb->posts;
$replacements[] = 'post_status';
$replacements[] = 'post_type';
$replacements = array_merge( $replacements, $post_types );
$replacements[] = $this->page_size;
$replacements[] = ( $offset_multiplier * $this->page_size );
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnsupportedPlaceholder,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Already prepared, and no cache applicable.
return $wpdb->get_results(
$wpdb->prepare(
'SELECT ' . implode( ', ', $this->selects )
. ' FROM %i AS posts '
. implode( ' ', $this->joins )
. ' WHERE posts.%i = "publish" AND posts.%i IN ('
. implode( ',', array_fill( 0, count( $post_types ), '%s' ) ) . ')'
. ' LIMIT %d OFFSET %d',
$replacements
),
ARRAY_A
);
// phpcs:enable
}
/**
* Prepares the necessary selects and joins to get all data in a single query.
*
* @param array $columns The columns we want our query to return.
*
* @return void
*/
public function set_columns( array $columns ) {
$this->columns = $columns;
$this->joins = [];
$this->selects = [ 'posts.ID', 'posts.post_type' ];
if ( in_array( 'title', $this->columns, true ) ) {
$this->selects[] = 'posts.post_title';
}
// If we're selecting keywords_score then we always want the keywords as well.
if ( in_array( 'keywords', $this->columns, true ) || in_array( 'keywords_score', $this->columns, true ) ) {
$this->add_meta_join( 'primary_keyword', WPSEO_Meta::$meta_prefix . 'focuskw' );
$this->add_meta_join( 'other_keywords', WPSEO_Meta::$meta_prefix . 'focuskeywords' );
}
if ( in_array( 'readability_score', $this->columns, true ) ) {
$this->add_meta_join( 'readability_score', WPSEO_Meta::$meta_prefix . 'content_score' );
}
if ( in_array( 'keywords_score', $this->columns, true ) ) {
// Score for other keywords is already in the other_keywords select so only join for the primary_keyword_score.
$this->add_meta_join( 'primary_keyword_score', WPSEO_Meta::$meta_prefix . 'linkdex' );
}
if ( in_array( 'seo_title', $this->columns, true ) ) {
$this->add_meta_join( 'seo_title', WPSEO_Meta::$meta_prefix . 'title' );
}
if ( in_array( 'meta_description', $this->columns, true ) ) {
$this->add_meta_join( 'meta_description', WPSEO_Meta::$meta_prefix . 'metadesc' );
}
}
/**
* Returns the page size for the query.
*
* @return int Page size that is being used.
*/
public function get_page_size() {
return $this->page_size;
}
/**
* Adds an aliased join to the $wpdb->postmeta table so that multiple meta values can be selected in a single row.
*
* While this function should never be used with user input,
* all non-word non-digit characters are removed from both params for increased robustness.
*
* @param string $alias The alias to use in our query output.
* @param string $key The meta_key to select.
*
* @return void
*/
protected function add_meta_join( $alias, $key ) {
global $wpdb;
$alias = preg_replace( '/[^\w\d]/', '', $alias );
$key = preg_replace( '/[^\w\d]/', '', $key );
$this->selects[] = $alias . '_join.meta_value AS ' . $alias;
$this->joins[] = 'LEFT OUTER JOIN ' . $wpdb->prefix . 'postmeta AS ' . $alias . '_join '
. 'ON ' . $alias . '_join.post_id = posts.ID '
. 'AND ' . $alias . '_join.meta_key = "' . $key . '"';
}
}