59517635615d055f783a2be92e52a1e2637df1be

Committed 17 Feb 2025 11:08AM UTC coverage: 53.377% (-1.3%) from 54.636%

Build # 59517635615d055f783a2be92e52a1e2637df1be

Build Type

Pull #22048

github

Committed by

web-flow

Commit Message

Merge e41dbb150 into 711656c23

Pull Request Pull Request #22048: Update Dashboard page description

Run Details

7808 of 13867 branches covered (56.31%)

Branch coverage included in aggregate %.

4 of 5 new or added lines in 2 files covered. (80.0%)

1554 existing lines in 42 files now uncovered.

30279 of 57488 relevant lines covered (52.67%)

40022.22 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

53.08

/src/repositories/indexable-cleanup-repository.php

<?php

namespace Yoast\WP\SEO\Repositories;

use mysqli_result;
use Yoast\WP\Lib\Model;
use Yoast\WP\Lib\ORM;
use Yoast\WP\SEO\Helpers\Author_Archive_Helper;
use Yoast\WP\SEO\Helpers\Post_Type_Helper;
use Yoast\WP\SEO\Helpers\Taxonomy_Helper;

/**
 * Repository containing all cleanup queries.
 */
class Indexable_Cleanup_Repository {

        /**
         * A helper for taxonomies.
         *
         * @var Taxonomy_Helper
         */
        private $taxonomy;

        /**
         * A helper for post types.
         *
         * @var Post_Type_Helper
         */
        private $post_type;

        /**
         * A helper for author archives.
         *
         * @var Author_Archive_Helper
         */
        private $author_archive;

        /**
         * The constructor.
         *
         * @param Taxonomy_Helper       $taxonomy       A helper for taxonomies.
         * @param Post_Type_Helper      $post_type      A helper for post types.
         * @param Author_Archive_Helper $author_archive A helper for author archives.
         */
        public function __construct( Taxonomy_Helper $taxonomy, Post_Type_Helper $post_type, Author_Archive_Helper $author_archive ) {
                $this->taxonomy       = $taxonomy;
                $this->post_type      = $post_type;
                $this->author_archive = $author_archive;
        }

        /**
         * Starts a query for this repository.
         *
         * @return ORM
         */
        public function query() {
                return Model::of_type( 'Indexable' );
        }

        /**
         * Deletes rows from the indexable table depending on the object_type and object_sub_type.
         *
         * @param string $object_type     The object type to query.
         * @param string $object_sub_type The object subtype to query.
         * @param int    $limit           The limit we'll apply to the delete query.
         *
         * @return int|bool The number of rows that was deleted or false if the query failed.
         */
        public function clean_indexables_with_object_type_and_object_sub_type( string $object_type, string $object_sub_type, int $limit ) {
                global $wpdb;

                $indexable_table = Model::get_table_name( 'Indexable' );

                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
                $sql = $wpdb->prepare( "DELETE FROM $indexable_table WHERE object_type = %s AND object_sub_type = %s ORDER BY id LIMIT %d", $object_type, $object_sub_type, $limit );

                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                return $wpdb->query( $sql );
        }

        /**
         * Counts amount of indexables by object type and object sub type.
         *
         * @param string $object_type     The object type to check.
         * @param string $object_sub_type The object sub type to check.
         *
         * @return float|int
         */
        public function count_indexables_with_object_type_and_object_sub_type( string $object_type, string $object_sub_type ) {
                return $this
                        ->query()
                        ->where( 'object_type', $object_type )
                        ->where( 'object_sub_type', $object_sub_type )
                        ->count();
        }

        /**
         * Deletes rows from the indexable table depending on the post_status.
         *
         * @param string $post_status The post status to query.
         * @param int    $limit       The limit we'll apply to the delete query.
         *
         * @return int|bool The number of rows that was deleted or false if the query failed.
         */
        public function clean_indexables_with_post_status( $post_status, $limit ) {
                global $wpdb;

                $indexable_table = Model::get_table_name( 'Indexable' );

                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
                $sql = $wpdb->prepare( "DELETE FROM $indexable_table WHERE object_type = 'post' AND post_status = %s ORDER BY id LIMIT %d", $post_status, $limit );

                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                return $wpdb->query( $sql );
        }

        /**
         * Counts indexables with a certain post status.
         *
         * @param string $post_status The post status to count.
         *
         * @return float|int
         */
        public function count_indexables_with_post_status( string $post_status ) {
                return $this
                        ->query()
                        ->where( 'object_type', 'post' )
                        ->where( 'post_status', $post_status )
                        ->count();
        }

        /**
         * Cleans up any indexables that belong to post types that are not/no longer publicly viewable.
         *
         * @param int $limit The limit we'll apply to the queries.
         *
         * @return bool|int The number of deleted rows, false if the query fails.
         */
        public function clean_indexables_for_non_publicly_viewable_post( $limit ) {
                global $wpdb;
                $indexable_table = Model::get_table_name( 'Indexable' );

                $included_post_types = $this->post_type->get_indexable_post_types();

                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
                if ( empty( $included_post_types ) ) {
                        $delete_query = $wpdb->prepare(
                                "DELETE FROM $indexable_table
                                WHERE object_type = 'post'
                                AND object_sub_type IS NOT NULL
                                LIMIT %d",
                                $limit
                        );
                }
                else {
                        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Reason: we're passing an array instead.
                        $delete_query = $wpdb->prepare(
                                "DELETE FROM $indexable_table
                                WHERE object_type = 'post'
                                AND object_sub_type IS NOT NULL
                                AND object_sub_type NOT IN ( " . \implode( ', ', \array_fill( 0, \count( $included_post_types ), '%s' ) ) . ' )
                                LIMIT %d',
                                \array_merge( $included_post_types, [ $limit ] )
                        );
                }
                // phpcs:enable

                // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
                // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
                // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
                return $wpdb->query( $delete_query );
                // phpcs:enable
        }

        /**
         * Counts all indexables for non public post types.
         *
         * @return float|int
         */
        public function count_indexables_for_non_publicly_viewable_post() {
                $included_post_types = $this->post_type->get_indexable_post_types();

                if ( empty( $included_post_types ) ) {
                        return $this
                                ->query()
                                ->where( 'object_type', 'post' )
                                ->where_not_equal( 'object_sub_type', 'null' )
                                ->count();
                }
                else {
                        return $this
                                ->query()
                                ->where( 'object_type', 'post' )
                                ->where_not_equal( 'object_sub_type', 'null' )
                                ->where_not_in( 'object_sub_type', $included_post_types )
                                ->count();
                }
        }

        /**
         * Cleans up any indexables that belong to taxonomies that are not/no longer publicly viewable.
         *
         * @param int $limit The limit we'll apply to the queries.
         *
         * @return bool|int The number of deleted rows, false if the query fails.
         */
        public function clean_indexables_for_non_publicly_viewable_taxonomies( $limit ) {
                global $wpdb;
                $indexable_table = Model::get_table_name( 'Indexable' );

                $included_taxonomies = $this->taxonomy->get_indexable_taxonomies();

                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
                if ( empty( $included_taxonomies ) ) {
                        $delete_query = $wpdb->prepare(
                                "DELETE FROM $indexable_table
                                WHERE object_type = 'term'
                                AND object_sub_type IS NOT NULL
                                LIMIT %d",
                                $limit
                        );
                }
                else {
                        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Reason: we're passing an array instead.
                        $delete_query = $wpdb->prepare(
                                "DELETE FROM $indexable_table
                                WHERE object_type = 'term'
                                AND object_sub_type IS NOT NULL
                                AND object_sub_type NOT IN ( " . \implode( ', ', \array_fill( 0, \count( $included_taxonomies ), '%s' ) ) . ' )
                                LIMIT %d',
                                \array_merge( $included_taxonomies, [ $limit ] )
                        );
                }
                // phpcs:enable

                // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
                // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
                // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
                return $wpdb->query( $delete_query );
                // phpcs:enable
        }

        /**
         * Cleans up any indexables that belong to post type archive page that are not/no longer publicly viewable.
         *
         * @param int $limit The limit we'll apply to the queries.
         *
         * @return bool|int The number of deleted rows, false if the query fails.
         */
        public function clean_indexables_for_non_publicly_viewable_post_type_archive_pages( $limit ) {
                global $wpdb;
                $indexable_table = Model::get_table_name( 'Indexable' );

                $included_post_types = $this->post_type->get_indexable_post_archives();

                $post_archives = [];

                foreach ( $included_post_types as $post_type ) {
                        $post_archives[] = $post_type->name;
                }
                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
                if ( empty( $post_archives ) ) {
                        $delete_query = $wpdb->prepare(
                                "DELETE FROM $indexable_table
                                WHERE object_type = 'post-type-archive'
                                AND object_sub_type IS NOT NULL
                                LIMIT %d",
                                $limit
                        );
                }
                else {
                        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Reason: we're passing an array instead.
                        $delete_query = $wpdb->prepare(
                                "DELETE FROM $indexable_table
                                WHERE object_type = 'post-type-archive'
                                AND object_sub_type IS NOT NULL
                                AND object_sub_type NOT IN ( " . \implode( ', ', \array_fill( 0, \count( $post_archives ), '%s' ) ) . ' )
                                LIMIT %d',
                                \array_merge( $post_archives, [ $limit ] )
                        );
                }
                // phpcs:enable

                // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
                // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
                // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
                return $wpdb->query( $delete_query );
                // phpcs:enable
        }

        /**
         * Counts indexables for non publicly viewable taxonomies.
         *
         * @return float|int
         */
        public function count_indexables_for_non_publicly_viewable_taxonomies() {
                $included_taxonomies = $this->taxonomy->get_indexable_taxonomies();
                if ( empty( $included_taxonomies ) ) {
                        return $this
                                ->query()
                                ->where( 'object_type', 'term' )
                                ->where_not_equal( 'object_sub_type', 'null' )
                                ->count();
                }
                else {
                        return $this
                                ->query()
                                ->where( 'object_type', 'term' )
                                ->where_not_equal( 'object_sub_type', 'null' )
                                ->where_not_in( 'object_sub_type', $included_taxonomies )
                                ->count();
                }
        }

        /**
         * Counts indexables for non publicly viewable taxonomies.
         *
         * @return float|int
         */
        public function count_indexables_for_non_publicly_post_type_archive_pages() {
                $included_post_types = $this->post_type->get_indexable_post_archives();

                $post_archives = [];

                foreach ( $included_post_types as $post_type ) {
                        $post_archives[] = $post_type->name;
                }
                if ( empty( $post_archives ) ) {
                        return $this
                                ->query()
                                ->where( 'object_type', 'post-type-archive' )
                                ->where_not_equal( 'object_sub_type', 'null' )
                                ->count();
                }

                return $this
                        ->query()
                        ->where( 'object_type', 'post-type-archive' )
                        ->where_not_equal( 'object_sub_type', 'null' )
                        ->where_not_in( 'object_sub_type', $post_archives )
                        ->count();
        }

        /**
         * Cleans up any user indexables when the author archives have been disabled.
         *
         * @param int $limit The limit we'll apply to the queries.
         *
         * @return bool|int The number of deleted rows, false if the query fails.
         */
        public function clean_indexables_for_authors_archive_disabled( $limit ) {
                global $wpdb;

                if ( ! $this->author_archive->are_disabled() ) {
                        return 0;
                }

                $indexable_table = Model::get_table_name( 'Indexable' );

                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
                $delete_query = $wpdb->prepare( "DELETE FROM $indexable_table WHERE object_type = 'user' LIMIT %d", $limit );
                // phpcs:enable

                // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
                // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
                // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
                return $wpdb->query( $delete_query );
                // phpcs:enable
        }

        /**
         * Counts the amount of author archive indexables if they are not disabled.
         *
         * @return float|int
         */
        public function count_indexables_for_authors_archive_disabled() {
                if ( ! $this->author_archive->are_disabled() ) {
                        return 0;
                }

                return $this
                        ->query()
                        ->where( 'object_type', 'user' )
                        ->count();
        }

        /**
         * Cleans up any indexables that belong to users that have their author archives disabled.
         *
         * @param int $limit The limit we'll apply to the queries.
         *
         * @return bool|int The number of deleted rows, false if the query fails.
         */
        public function clean_indexables_for_authors_without_archive( $limit ) {
                global $wpdb;

                $indexable_table           = Model::get_table_name( 'Indexable' );
                $author_archive_post_types = $this->author_archive->get_author_archive_post_types();
                $viewable_post_stati       = \array_filter( \get_post_stati(), 'is_post_status_viewable' );

                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
                // phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Reason: we're passing an array instead.
                $delete_query = $wpdb->prepare(
                        "DELETE FROM $indexable_table
                                WHERE object_type = 'user'
                                AND object_id NOT IN (
                                        SELECT DISTINCT post_author
                                        FROM $wpdb->posts
                                        WHERE post_type IN ( " . \implode( ', ', \array_fill( 0, \count( $author_archive_post_types ), '%s' ) ) . ' )
                                        AND post_status IN ( ' . \implode( ', ', \array_fill( 0, \count( $viewable_post_stati ), '%s' ) ) . ' )
                                ) LIMIT %d',
                        \array_merge( $author_archive_post_types, $viewable_post_stati, [ $limit ] )
                );
                // phpcs:enable

                // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
                // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
                // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
                return $wpdb->query( $delete_query );
                // phpcs:enable
        }

        /**
         * Counts total amount of indexables for authors without archives.
         *
         * @return bool|int|mysqli_result|resource|null
         */
        public function count_indexables_for_authors_without_archive() {
                global $wpdb;

                $indexable_table           = Model::get_table_name( 'Indexable' );
                $author_archive_post_types = $this->author_archive->get_author_archive_post_types();
                $viewable_post_stati       = \array_filter( \get_post_stati(), 'is_post_status_viewable' );

                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
                // phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Reason: we're passing an array instead.
                $count_query = $wpdb->prepare(
                        "SELECT count(*) FROM $indexable_table
                                WHERE object_type = 'user'
                                AND object_id NOT IN (
                                        SELECT DISTINCT post_author
                                        FROM $wpdb->posts
                                        WHERE post_type IN ( " . \implode( ', ', \array_fill( 0, \count( $author_archive_post_types ), '%s' ) ) . ' )
                                        AND post_status IN ( ' . \implode( ', ', \array_fill( 0, \count( $viewable_post_stati ), '%s' ) ) . ' )
                                )',
                        \array_merge( $author_archive_post_types, $viewable_post_stati )
                );
                // phpcs:enable

                // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
                // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
                // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
                return $wpdb->get_col( $count_query )[0];
                // phpcs:enable
        }

        /**
         * Deletes rows from the indexable table where the source is no longer there.
         *
         * @param string $source_table      The source table which we need to check the indexables against.
         * @param string $source_identifier The identifier which the indexables are matched to.
         * @param string $object_type       The indexable object type.
         * @param int    $limit             The limit we'll apply to the delete query.
         *
         * @return int|bool The number of rows that was deleted or false if the query failed.
         */
        public function clean_indexables_for_object_type_and_source_table( $source_table, $source_identifier, $object_type, $limit ) {
                global $wpdb;

                $indexable_table = Model::get_table_name( 'Indexable' );
                $source_table    = $wpdb->prefix . $source_table;
                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
                $query = $wpdb->prepare(
                        "
                        SELECT indexable_table.object_id
                        FROM {$indexable_table} indexable_table
                        LEFT JOIN {$source_table} AS source_table
                        ON indexable_table.object_id = source_table.{$source_identifier}
                        WHERE source_table.{$source_identifier} IS NULL
                        AND indexable_table.object_id IS NOT NULL
                        AND indexable_table.object_type = '{$object_type}'
                        LIMIT %d",
                        $limit
                );
                // phpcs:enable

                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                $orphans = $wpdb->get_col( $query );

                if ( empty( $orphans ) ) {
                        return 0;
                }

                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                return $wpdb->query( "DELETE FROM $indexable_table WHERE object_type = '{$object_type}' AND object_id IN( " . \implode( ',', $orphans ) . ' )' );
        }

        /**
         * Deletes rows from the indexable table where the source is no longer there.
         *
         * @param int $limit The limit we'll apply to the delete query.
         *
         * @return int|bool The number of rows that was deleted or false if the query failed.
         */
        public function clean_indexables_for_orphaned_users( $limit ) {
                global $wpdb;

                $indexable_table = Model::get_table_name( 'Indexable' );
                $source_table    = $wpdb->users;
                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
                $query = $wpdb->prepare(
                        "
                        SELECT indexable_table.object_id
                        FROM {$indexable_table} indexable_table
                        LEFT JOIN {$source_table} AS source_table
                        ON indexable_table.object_id = source_table.ID
                        WHERE source_table.ID IS NULL
                        AND indexable_table.object_id IS NOT NULL
                        AND indexable_table.object_type = 'user'
                        LIMIT %d",
                        $limit
                );
                // phpcs:enable

                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                $orphans = $wpdb->get_col( $query );

                if ( empty( $orphans ) ) {
                        return 0;
                }

                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                return $wpdb->query( "DELETE FROM $indexable_table WHERE object_type = 'user' AND object_id IN( " . \implode( ',', $orphans ) . ' )' );
        }

        /**
         * Counts indexables for given source table + source identifier + object type.
         *
         * @param string $source_table      The source table.
         * @param string $source_identifier The source identifier.
         * @param string $object_type       The object type.
         *
         * @return mixed
         */
        public function count_indexables_for_object_type_and_source_table( string $source_table, string $source_identifier, string $object_type ) {
                global $wpdb;
                $indexable_table = Model::get_table_name( 'Indexable' );
                $source_table    = $wpdb->prefix . $source_table;
                // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                return $wpdb->get_col(
                        "
                        SELECT count(*)
                        FROM {$indexable_table} indexable_table
                        LEFT JOIN {$source_table} AS source_table
                        ON indexable_table.object_id = source_table.{$source_identifier}
                        WHERE source_table.{$source_identifier} IS NULL
                        AND indexable_table.object_id IS NOT NULL
                        AND indexable_table.object_type = '{$object_type}'"
                )[0];
                // phpcs:enable
        }

        /**
         * Counts indexables for orphaned users.
         *
         * @return mixed
         */
        public function count_indexables_for_orphaned_users() {
                global $wpdb;
                $indexable_table = Model::get_table_name( 'Indexable' );
                $source_table    = $wpdb->users;
                //phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                return $wpdb->get_col(
                        "
                        SELECT count(*)
                        FROM {$indexable_table} indexable_table
                        LEFT JOIN {$source_table} AS source_table
                        ON indexable_table.object_id = source_table.ID
                        WHERE source_table.ID IS NULL
                        AND indexable_table.object_id IS NOT NULL
                        AND indexable_table.object_type = 'user'"
                )[0];
                // phpcs:enable
        }

        /**
         * Cleans orphaned rows from a yoast table.
         *
         * @param string $table  The table to clean up.
         * @param string $column The table column the cleanup will rely on.
         * @param int    $limit  The limit we'll apply to the queries.
         *
         * @return int|bool The number of deleted rows, false if the query fails.
         */
        public function cleanup_orphaned_from_table( $table, $column, $limit ) {
                global $wpdb;

                $table           = Model::get_table_name( $table );
                $indexable_table = Model::get_table_name( 'Indexable' );

                // Warning: If this query is changed, make sure to update the query in cleanup_orphaned_from_table in Premium as well.
                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
                $query = $wpdb->prepare(
                        "
                        SELECT table_to_clean.{$column}
                        FROM {$table} table_to_clean
                        LEFT JOIN {$indexable_table} AS indexable_table
                        ON table_to_clean.{$column} = indexable_table.id
                        WHERE indexable_table.id IS NULL
                        AND table_to_clean.{$column} IS NOT NULL
                        LIMIT %d",
                        $limit
                );
                // phpcs:enable

                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                $orphans = $wpdb->get_col( $query );

                if ( empty( $orphans ) ) {
                        return 0;
                }

                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                return $wpdb->query( "DELETE FROM $table WHERE {$column} IN( " . \implode( ',', $orphans ) . ' )' );
        }

        /**
         * Counts orphaned rows from a yoast table.
         *
         * @param string $table  The table to clean up.
         * @param string $column The table column the cleanup will rely on.
         *
         * @return int|bool The number of deleted rows, false if the query fails.
         */
        public function count_orphaned_from_table( string $table, string $column ) {
                global $wpdb;

                $table           = Model::get_table_name( $table );
                $indexable_table = Model::get_table_name( 'Indexable' );

                // Warning: If this query is changed, make sure to update the query in cleanup_orphaned_from_table in Premium as well.
                // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                return $wpdb->get_col(
                        "
                        SELECT count(*)
                        FROM {$table} table_to_clean
                        LEFT JOIN {$indexable_table} AS indexable_table
                        ON table_to_clean.{$column} = indexable_table.id
                        WHERE indexable_table.id IS NULL
                        AND table_to_clean.{$column} IS NOT NULL"
                )[0];
                // phpcs:enable
        }

        /**
         * Updates the author_id of indexables which author_id is not in the wp_users table with the id of the reassingned
         * user.
         *
         * @param int $limit The limit we'll apply to the queries.
         *
         * @return int|bool The number of updated rows, false if query to get data fails.
         */
        public function update_indexables_author_to_reassigned( $limit ) {
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                $reassigned_authors_objs = $this->get_reassigned_authors( $limit );

                if ( $reassigned_authors_objs === false ) {
                        return false;
                }

                return $this->update_indexable_authors( $reassigned_authors_objs, $limit );
        }

        /**
         * Fetches pairs of old_id -> new_id indexed by old_id.
         * By using the old_id (i.e. the id of the user that has been deleted) as key of the associative array, we can
         * easily compose an array of unique pairs of old_id -> new_id.
         *
         * @param int $limit The limit we'll apply to the queries.
         *
         * @return int|bool The associative array with shape [ old_id => [ old_id, new_author ] ] or false if query to get
         *                  data fails.
         */
        private function get_reassigned_authors( $limit ) {
                global $wpdb;

                $indexable_table = Model::get_table_name( 'Indexable' );
                $posts_table     = $wpdb->posts;

                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
                $query = $wpdb->prepare(
                        "
                        SELECT {$indexable_table}.author_id, {$posts_table}.post_author
                        FROM {$indexable_table} JOIN {$posts_table} on {$indexable_table}.object_id = {$posts_table}.id
                        WHERE object_type='post'
                        AND {$indexable_table}.author_id <> {$posts_table}.post_author
                        ORDER BY {$indexable_table}.author_id
                        LIMIT %d",
                        $limit
                );
                // phpcs:enable

                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                return $wpdb->get_results( $query, \OBJECT_K );
        }

        /**
         * Updates the indexable's author_id referring to a deleted author with the id of the reassigned user.
         *
         * @param array $reassigned_authors_objs The array of objects with shape [ old_id => [ old_id, new_id ] ].
         * @param int   $limit                   The limit we'll apply to the queries.
         *
         * @return int|bool The associative array with shape [ old_id => [ old_id, new_author ] ] or false if query to get
         *                  data fails.
         */
        private function update_indexable_authors( $reassigned_authors_objs, $limit ) {
                global $wpdb;

                $indexable_table = Model::get_table_name( 'Indexable' );

                // This is a workaround for the fact that the array_column function does not work on objects in PHP 5.6.
                $reassigned_authors_array = \array_map(
                        static function ( $obj ) {
                                return (array) $obj;
                        },
                        $reassigned_authors_objs
                );

                $reassigned_authors = \array_combine( \array_column( $reassigned_authors_array, 'author_id' ), \array_column( $reassigned_authors_array, 'post_author' ) );

                foreach ( $reassigned_authors as $old_author_id => $new_author_id ) {
                        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
                        $query = $wpdb->prepare(
                                "
                                UPDATE {$indexable_table}
                                SET {$indexable_table}.author_id = {$new_author_id}
                                WHERE {$indexable_table}.author_id = {$old_author_id}
                                AND object_type='post'
                                LIMIT %d",
                                $limit
                        );
                        // phpcs:enable

                        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
                        $wpdb->query( $query );
                }

                return \count( $reassigned_authors );
        }
}

Yoast / wordpress-seo / 59517635615d055f783a2be92e52a1e2637df1be

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous