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

indexable-cleanup-repository.php in Yoast SEO – Advanced SEO with real-time guidance and built-in AI 27.7, at src/repositories/indexable-cleanup-repository.php

752 lines 27.2 KB

Raw Download Zip

1	<?php
2
3	namespace Yoast\WP\SEO\Repositories;
4
5	use mysqli_result;
6	use Yoast\WP\Lib\Model;
7	use Yoast\WP\Lib\ORM;
8	use Yoast\WP\SEO\Helpers\Author_Archive_Helper;
9	use Yoast\WP\SEO\Helpers\Post_Type_Helper;
10	use Yoast\WP\SEO\Helpers\Taxonomy_Helper;
11
12	/**
13	* Repository containing all cleanup queries.
14	*/
15	class Indexable_Cleanup_Repository {
16
17	/**
18	* A helper for taxonomies.
19	*
20	* @var Taxonomy_Helper
21	*/
22	private $taxonomy;
23
24	/**
25	* A helper for post types.
26	*
27	* @var Post_Type_Helper
28	*/
29	private $post_type;
30
31	/**
32	* A helper for author archives.
33	*
34	* @var Author_Archive_Helper
35	*/
36	private $author_archive;
37
38	/**
39	* The constructor.
40	*
41	* @param Taxonomy_Helper $taxonomy A helper for taxonomies.
42	* @param Post_Type_Helper $post_type A helper for post types.
43	* @param Author_Archive_Helper $author_archive A helper for author archives.
44	*/
45	public function __construct( Taxonomy_Helper $taxonomy, Post_Type_Helper $post_type, Author_Archive_Helper $author_archive ) {
46	$this->taxonomy = $taxonomy;
47	$this->post_type = $post_type;
48	$this->author_archive = $author_archive;
49	}
50
51	/**
52	* Starts a query for this repository.
53	*
54	* @return ORM
55	*/
56	public function query() {
57	return Model::of_type( 'Indexable' );
58	}
59
60	/**
61	* Deletes rows from the indexable table depending on the object_type and object_sub_type.
62	*
63	* @param string $object_type The object type to query.
64	* @param string $object_sub_type The object subtype to query.
65	* @param int $limit The limit we'll apply to the delete query.
66	*
67	* @return int\|bool The number of rows that was deleted or false if the query failed.
68	*/
69	public function clean_indexables_with_object_type_and_object_sub_type( string $object_type, string $object_sub_type, int $limit ) {
70	global $wpdb;
71
72	$indexable_table = Model::get_table_name( 'Indexable' );
73
74	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
75	$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 );
76
77	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
78	return $wpdb->query( $sql );
79	}
80
81	/**
82	* Counts amount of indexables by object type and object sub type.
83	*
84	* @param string $object_type The object type to check.
85	* @param string $object_sub_type The object sub type to check.
86	*
87	* @return float\|int
88	*/
89	public function count_indexables_with_object_type_and_object_sub_type( string $object_type, string $object_sub_type ) {
90	return $this
91	->query()
92	->where( 'object_type', $object_type )
93	->where( 'object_sub_type', $object_sub_type )
94	->count();
95	}
96
97	/**
98	* Deletes rows from the indexable table depending on the post_status.
99	*
100	* @param string $post_status The post status to query.
101	* @param int $limit The limit we'll apply to the delete query.
102	*
103	* @return int\|bool The number of rows that was deleted or false if the query failed.
104	*/
105	public function clean_indexables_with_post_status( $post_status, $limit ) {
106	global $wpdb;
107
108	$indexable_table = Model::get_table_name( 'Indexable' );
109
110	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
111	$sql = $wpdb->prepare( "DELETE FROM $indexable_table WHERE object_type = 'post' AND post_status = %s ORDER BY id LIMIT %d", $post_status, $limit );
112
113	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
114	return $wpdb->query( $sql );
115	}
116
117	/**
118	* Counts indexables with a certain post status.
119	*
120	* @param string $post_status The post status to count.
121	*
122	* @return float\|int
123	*/
124	public function count_indexables_with_post_status( string $post_status ) {
125	return $this
126	->query()
127	->where( 'object_type', 'post' )
128	->where( 'post_status', $post_status )
129	->count();
130	}
131
132	/**
133	* Cleans up any indexables that belong to post types that are not/no longer publicly viewable.
134	*
135	* @param int $limit The limit we'll apply to the queries.
136	*
137	* @return bool\|int The number of deleted rows, false if the query fails.
138	*/
139	public function clean_indexables_for_non_publicly_viewable_post( $limit ) {
140	global $wpdb;
141	$indexable_table = Model::get_table_name( 'Indexable' );
142
143	$included_post_types = $this->post_type->get_indexable_post_types();
144
145	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
146	if ( empty( $included_post_types ) ) {
147	$delete_query = $wpdb->prepare(
148	"DELETE FROM $indexable_table
149	WHERE object_type = 'post'
150	AND object_sub_type IS NOT NULL
151	LIMIT %d",
152	$limit,
153	);
154	}
155	else {
156	// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Reason: we're passing an array instead.
157	$delete_query = $wpdb->prepare(
158	"DELETE FROM $indexable_table
159	WHERE object_type = 'post'
160	AND object_sub_type IS NOT NULL
161	AND object_sub_type NOT IN ( " . \implode( ', ', \array_fill( 0, \count( $included_post_types ), '%s' ) ) . ' )
162	LIMIT %d',
163	\array_merge( $included_post_types, [ $limit ] ),
164	);
165	}
166	// phpcs:enable
167
168	// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
169	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
170	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
171	return $wpdb->query( $delete_query );
172	// phpcs:enable
173	}
174
175	/**
176	* Counts all indexables for non public post types.
177	*
178	* @return float\|int
179	*/
180	public function count_indexables_for_non_publicly_viewable_post() {
181	$included_post_types = $this->post_type->get_indexable_post_types();
182
183	if ( empty( $included_post_types ) ) {
184	return $this
185	->query()
186	->where( 'object_type', 'post' )
187	->where_not_equal( 'object_sub_type', 'null' )
188	->count();
189	}
190	else {
191	return $this
192	->query()
193	->where( 'object_type', 'post' )
194	->where_not_equal( 'object_sub_type', 'null' )
195	->where_not_in( 'object_sub_type', $included_post_types )
196	->count();
197	}
198	}
199
200	/**
201	* Cleans up any indexables that belong to taxonomies that are not/no longer publicly viewable.
202	*
203	* @param int $limit The limit we'll apply to the queries.
204	*
205	* @return bool\|int The number of deleted rows, false if the query fails.
206	*/
207	public function clean_indexables_for_non_publicly_viewable_taxonomies( $limit ) {
208	global $wpdb;
209	$indexable_table = Model::get_table_name( 'Indexable' );
210
211	$included_taxonomies = $this->taxonomy->get_indexable_taxonomies();
212
213	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
214	if ( empty( $included_taxonomies ) ) {
215	$delete_query = $wpdb->prepare(
216	"DELETE FROM $indexable_table
217	WHERE object_type = 'term'
218	AND object_sub_type IS NOT NULL
219	LIMIT %d",
220	$limit,
221	);
222	}
223	else {
224	// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Reason: we're passing an array instead.
225	$delete_query = $wpdb->prepare(
226	"DELETE FROM $indexable_table
227	WHERE object_type = 'term'
228	AND object_sub_type IS NOT NULL
229	AND object_sub_type NOT IN ( " . \implode( ', ', \array_fill( 0, \count( $included_taxonomies ), '%s' ) ) . ' )
230	LIMIT %d',
231	\array_merge( $included_taxonomies, [ $limit ] ),
232	);
233	}
234	// phpcs:enable
235
236	// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
237	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
238	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
239	return $wpdb->query( $delete_query );
240	// phpcs:enable
241	}
242
243	/**
244	* Cleans up any indexables that belong to post type archive page that are not/no longer publicly viewable.
245	*
246	* @param int $limit The limit we'll apply to the queries.
247	*
248	* @return bool\|int The number of deleted rows, false if the query fails.
249	*/
250	public function clean_indexables_for_non_publicly_viewable_post_type_archive_pages( $limit ) {
251	global $wpdb;
252	$indexable_table = Model::get_table_name( 'Indexable' );
253
254	$included_post_types = $this->post_type->get_indexable_post_archives();
255
256	$post_archives = [];
257
258	foreach ( $included_post_types as $post_type ) {
259	$post_archives[] = $post_type->name;
260	}
261	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
262	if ( empty( $post_archives ) ) {
263	$delete_query = $wpdb->prepare(
264	"DELETE FROM $indexable_table
265	WHERE object_type = 'post-type-archive'
266	AND object_sub_type IS NOT NULL
267	LIMIT %d",
268	$limit,
269	);
270	}
271	else {
272	// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Reason: we're passing an array instead.
273	$delete_query = $wpdb->prepare(
274	"DELETE FROM $indexable_table
275	WHERE object_type = 'post-type-archive'
276	AND object_sub_type IS NOT NULL
277	AND object_sub_type NOT IN ( " . \implode( ', ', \array_fill( 0, \count( $post_archives ), '%s' ) ) . ' )
278	LIMIT %d',
279	\array_merge( $post_archives, [ $limit ] ),
280	);
281	}
282	// phpcs:enable
283
284	// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
285	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
286	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
287	return $wpdb->query( $delete_query );
288	// phpcs:enable
289	}
290
291	/**
292	* Counts indexables for non publicly viewable taxonomies.
293	*
294	* @return float\|int
295	*/
296	public function count_indexables_for_non_publicly_viewable_taxonomies() {
297	$included_taxonomies = $this->taxonomy->get_indexable_taxonomies();
298	if ( empty( $included_taxonomies ) ) {
299	return $this
300	->query()
301	->where( 'object_type', 'term' )
302	->where_not_equal( 'object_sub_type', 'null' )
303	->count();
304	}
305	else {
306	return $this
307	->query()
308	->where( 'object_type', 'term' )
309	->where_not_equal( 'object_sub_type', 'null' )
310	->where_not_in( 'object_sub_type', $included_taxonomies )
311	->count();
312	}
313	}
314
315	/**
316	* Counts indexables for non publicly viewable taxonomies.
317	*
318	* @return float\|int
319	*/
320	public function count_indexables_for_non_publicly_post_type_archive_pages() {
321	$included_post_types = $this->post_type->get_indexable_post_archives();
322
323	$post_archives = [];
324
325	foreach ( $included_post_types as $post_type ) {
326	$post_archives[] = $post_type->name;
327	}
328	if ( empty( $post_archives ) ) {
329	return $this
330	->query()
331	->where( 'object_type', 'post-type-archive' )
332	->where_not_equal( 'object_sub_type', 'null' )
333	->count();
334	}
335
336	return $this
337	->query()
338	->where( 'object_type', 'post-type-archive' )
339	->where_not_equal( 'object_sub_type', 'null' )
340	->where_not_in( 'object_sub_type', $post_archives )
341	->count();
342	}
343
344	/**
345	* Cleans up any user indexables when the author archives have been disabled.
346	*
347	* @param int $limit The limit we'll apply to the queries.
348	*
349	* @return bool\|int The number of deleted rows, false if the query fails.
350	*/
351	public function clean_indexables_for_authors_archive_disabled( $limit ) {
352	global $wpdb;
353
354	if ( ! $this->author_archive->are_disabled() ) {
355	return 0;
356	}
357
358	$indexable_table = Model::get_table_name( 'Indexable' );
359
360	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
361	$delete_query = $wpdb->prepare( "DELETE FROM $indexable_table WHERE object_type = 'user' LIMIT %d", $limit );
362	// phpcs:enable
363
364	// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
365	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
366	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
367	return $wpdb->query( $delete_query );
368	// phpcs:enable
369	}
370
371	/**
372	* Counts the amount of author archive indexables if they are not disabled.
373	*
374	* @return float\|int
375	*/
376	public function count_indexables_for_authors_archive_disabled() {
377	if ( ! $this->author_archive->are_disabled() ) {
378	return 0;
379	}
380
381	return $this
382	->query()
383	->where( 'object_type', 'user' )
384	->count();
385	}
386
387	/**
388	* Cleans up any indexables that belong to users that have their author archives disabled.
389	*
390	* @param int $limit The limit we'll apply to the queries.
391	*
392	* @return bool\|int The number of deleted rows, false if the query fails.
393	*/
394	public function clean_indexables_for_authors_without_archive( $limit ) {
395	global $wpdb;
396
397	$indexable_table = Model::get_table_name( 'Indexable' );
398	$author_archive_post_types = $this->author_archive->get_author_archive_post_types();
399	$viewable_post_stati = \array_filter( \get_post_stati(), 'is_post_status_viewable' );
400
401	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
402	// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Reason: we're passing an array instead.
403	$delete_query = $wpdb->prepare(
404	"DELETE FROM $indexable_table
405	WHERE object_type = 'user'
406	AND object_id NOT IN (
407	SELECT DISTINCT post_author
408	FROM $wpdb->posts
409	WHERE post_type IN ( " . \implode( ', ', \array_fill( 0, \count( $author_archive_post_types ), '%s' ) ) . ' )
410	AND post_status IN ( ' . \implode( ', ', \array_fill( 0, \count( $viewable_post_stati ), '%s' ) ) . ' )
411	) LIMIT %d',
412	\array_merge( $author_archive_post_types, $viewable_post_stati, [ $limit ] ),
413	);
414	// phpcs:enable
415
416	// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
417	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
418	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
419	return $wpdb->query( $delete_query );
420	// phpcs:enable
421	}
422
423	/**
424	* Counts total amount of indexables for authors without archives.
425	*
426	* @return bool\|int\|mysqli_result\|resource\|null
427	*/
428	public function count_indexables_for_authors_without_archive() {
429	global $wpdb;
430
431	$indexable_table = Model::get_table_name( 'Indexable' );
432	$author_archive_post_types = $this->author_archive->get_author_archive_post_types();
433	$viewable_post_stati = \array_filter( \get_post_stati(), 'is_post_status_viewable' );
434
435	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: Too hard to fix.
436	// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Reason: we're passing an array instead.
437	$count_query = $wpdb->prepare(
438	"SELECT count(*) FROM $indexable_table
439	WHERE object_type = 'user'
440	AND object_id NOT IN (
441	SELECT DISTINCT post_author
442	FROM $wpdb->posts
443	WHERE post_type IN ( " . \implode( ', ', \array_fill( 0, \count( $author_archive_post_types ), '%s' ) ) . ' )
444	AND post_status IN ( ' . \implode( ', ', \array_fill( 0, \count( $viewable_post_stati ), '%s' ) ) . ' )
445	)',
446	\array_merge( $author_archive_post_types, $viewable_post_stati ),
447	);
448	// phpcs:enable
449
450	// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: No relevant caches.
451	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reason: Most performant way.
452	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Reason: Is it prepared already.
453	return $wpdb->get_col( $count_query )[0];
454	// phpcs:enable
455	}
456
457	/**
458	* Deletes rows from the indexable table where the source is no longer there.
459	*
460	* @param string $source_table The source table which we need to check the indexables against.
461	* @param string $source_identifier The identifier which the indexables are matched to.
462	* @param string $object_type The indexable object type.
463	* @param int $limit The limit we'll apply to the delete query.
464	*
465	* @return int\|bool The number of rows that was deleted or false if the query failed.
466	*/
467	public function clean_indexables_for_object_type_and_source_table( $source_table, $source_identifier, $object_type, $limit ) {
468	global $wpdb;
469
470	$indexable_table = Model::get_table_name( 'Indexable' );
471	$source_table = $wpdb->prefix . $source_table;
472	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
473	$query = $wpdb->prepare(
474	"
475	SELECT indexable_table.object_id
476	FROM {$indexable_table} indexable_table
477	LEFT JOIN {$source_table} AS source_table
478	ON indexable_table.object_id = source_table.{$source_identifier}
479	WHERE source_table.{$source_identifier} IS NULL
480	AND indexable_table.object_id IS NOT NULL
481	AND indexable_table.object_type = '{$object_type}'
482	LIMIT %d",
483	$limit,
484	);
485	// phpcs:enable
486
487	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
488	$orphans = $wpdb->get_col( $query );
489
490	if ( empty( $orphans ) ) {
491	return 0;
492	}
493
494	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
495	return $wpdb->query( "DELETE FROM $indexable_table WHERE object_type = '{$object_type}' AND object_id IN( " . \implode( ',', $orphans ) . ' )' );
496	}
497
498	/**
499	* Deletes rows from the indexable table where the source is no longer there.
500	*
501	* @param int $limit The limit we'll apply to the delete query.
502	*
503	* @return int\|bool The number of rows that was deleted or false if the query failed.
504	*/
505	public function clean_indexables_for_orphaned_users( $limit ) {
506	global $wpdb;
507
508	$indexable_table = Model::get_table_name( 'Indexable' );
509	$source_table = $wpdb->users;
510	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
511	$query = $wpdb->prepare(
512	"
513	SELECT indexable_table.object_id
514	FROM {$indexable_table} indexable_table
515	LEFT JOIN {$source_table} AS source_table
516	ON indexable_table.object_id = source_table.ID
517	WHERE source_table.ID IS NULL
518	AND indexable_table.object_id IS NOT NULL
519	AND indexable_table.object_type = 'user'
520	LIMIT %d",
521	$limit,
522	);
523	// phpcs:enable
524
525	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
526	$orphans = $wpdb->get_col( $query );
527
528	if ( empty( $orphans ) ) {
529	return 0;
530	}
531
532	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
533	return $wpdb->query( "DELETE FROM $indexable_table WHERE object_type = 'user' AND object_id IN( " . \implode( ',', $orphans ) . ' )' );
534	}
535
536	/**
537	* Counts indexables for given source table + source identifier + object type.
538	*
539	* @param string $source_table The source table.
540	* @param string $source_identifier The source identifier.
541	* @param string $object_type The object type.
542	*
543	* @return mixed
544	*/
545	public function count_indexables_for_object_type_and_source_table( string $source_table, string $source_identifier, string $object_type ) {
546	global $wpdb;
547	$indexable_table = Model::get_table_name( 'Indexable' );
548	$source_table = $wpdb->prefix . $source_table;
549	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
550	return $wpdb->get_col(
551	"
552	SELECT count(*)
553	FROM {$indexable_table} indexable_table
554	LEFT JOIN {$source_table} AS source_table
555	ON indexable_table.object_id = source_table.{$source_identifier}
556	WHERE source_table.{$source_identifier} IS NULL
557	AND indexable_table.object_id IS NOT NULL
558	AND indexable_table.object_type = '{$object_type}'",
559	)[0];
560	// phpcs:enable
561	}
562
563	/**
564	* Counts indexables for orphaned users.
565	*
566	* @return mixed
567	*/
568	public function count_indexables_for_orphaned_users() {
569	global $wpdb;
570	$indexable_table = Model::get_table_name( 'Indexable' );
571	$source_table = $wpdb->users;
572	//phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
573	return $wpdb->get_col(
574	"
575	SELECT count(*)
576	FROM {$indexable_table} indexable_table
577	LEFT JOIN {$source_table} AS source_table
578	ON indexable_table.object_id = source_table.ID
579	WHERE source_table.ID IS NULL
580	AND indexable_table.object_id IS NOT NULL
581	AND indexable_table.object_type = 'user'",
582	)[0];
583	// phpcs:enable
584	}
585
586	/**
587	* Cleans orphaned rows from a yoast table.
588	*
589	* @param string $table The table to clean up.
590	* @param string $column The table column the cleanup will rely on.
591	* @param int $limit The limit we'll apply to the queries.
592	*
593	* @return int\|bool The number of deleted rows, false if the query fails.
594	*/
595	public function cleanup_orphaned_from_table( $table, $column, $limit ) {
596	global $wpdb;
597
598	$table = Model::get_table_name( $table );
599	$indexable_table = Model::get_table_name( 'Indexable' );
600
601	// Warning: If this query is changed, make sure to update the query in cleanup_orphaned_from_table in Premium as well.
602	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
603	$query = $wpdb->prepare(
604	"
605	SELECT table_to_clean.{$column}
606	FROM {$table} table_to_clean
607	LEFT JOIN {$indexable_table} AS indexable_table
608	ON table_to_clean.{$column} = indexable_table.id
609	WHERE indexable_table.id IS NULL
610	AND table_to_clean.{$column} IS NOT NULL
611	LIMIT %d",
612	$limit,
613	);
614	// phpcs:enable
615
616	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
617	$orphans = $wpdb->get_col( $query );
618
619	if ( empty( $orphans ) ) {
620	return 0;
621	}
622
623	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
624	return $wpdb->query( "DELETE FROM $table WHERE {$column} IN( " . \implode( ',', $orphans ) . ' )' );
625	}
626
627	/**
628	* Counts orphaned rows from a yoast table.
629	*
630	* @param string $table The table to clean up.
631	* @param string $column The table column the cleanup will rely on.
632	*
633	* @return int\|bool The number of deleted rows, false if the query fails.
634	*/
635	public function count_orphaned_from_table( string $table, string $column ) {
636	global $wpdb;
637
638	$table = Model::get_table_name( $table );
639	$indexable_table = Model::get_table_name( 'Indexable' );
640
641	// Warning: If this query is changed, make sure to update the query in cleanup_orphaned_from_table in Premium as well.
642	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
643	return $wpdb->get_col(
644	"
645	SELECT count(*)
646	FROM {$table} table_to_clean
647	LEFT JOIN {$indexable_table} AS indexable_table
648	ON table_to_clean.{$column} = indexable_table.id
649	WHERE indexable_table.id IS NULL
650	AND table_to_clean.{$column} IS NOT NULL",
651	)[0];
652	// phpcs:enable
653	}
654
655	/**
656	* Updates the author_id of indexables which author_id is not in the wp_users table with the id of the reassingned
657	* user.
658	*
659	* @param int $limit The limit we'll apply to the queries.
660	*
661	* @return int\|bool The number of updated rows, false if query to get data fails.
662	*/
663	public function update_indexables_author_to_reassigned( $limit ) {
664	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
665	$reassigned_authors_objs = $this->get_reassigned_authors( $limit );
666
667	if ( $reassigned_authors_objs === false ) {
668	return false;
669	}
670
671	return $this->update_indexable_authors( $reassigned_authors_objs, $limit );
672	}
673
674	/**
675	* Fetches pairs of old_id -> new_id indexed by old_id.
676	* By using the old_id (i.e. the id of the user that has been deleted) as key of the associative array, we can
677	* easily compose an array of unique pairs of old_id -> new_id.
678	*
679	* @param int $limit The limit we'll apply to the queries.
680	*
681	* @return int\|bool The associative array with shape [ old_id => [ old_id, new_author ] ] or false if query to get
682	* data fails.
683	*/
684	private function get_reassigned_authors( $limit ) {
685	global $wpdb;
686
687	$indexable_table = Model::get_table_name( 'Indexable' );
688	$posts_table = $wpdb->posts;
689
690	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
691	$query = $wpdb->prepare(
692	"
693	SELECT {$indexable_table}.author_id, {$posts_table}.post_author
694	FROM {$indexable_table} JOIN {$posts_table} on {$indexable_table}.object_id = {$posts_table}.id
695	WHERE object_type='post'
696	AND {$indexable_table}.author_id <> {$posts_table}.post_author
697	GROUP BY {$indexable_table}.author_id, {$posts_table}.post_author
698	ORDER BY {$indexable_table}.author_id
699	LIMIT %d",
700	$limit,
701	);
702	// phpcs:enable
703
704	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
705	return $wpdb->get_results( $query, \OBJECT_K );
706	}
707
708	/**
709	* Updates the indexable's author_id referring to a deleted author with the id of the reassigned user.
710	*
711	* @param array $reassigned_authors_objs The array of objects with shape [ old_id => [ old_id, new_id ] ].
712	* @param int $limit The limit we'll apply to the queries.
713	*
714	* @return int\|bool The associative array with shape [ old_id => [ old_id, new_author ] ] or false if query to get
715	* data fails.
716	*/
717	private function update_indexable_authors( $reassigned_authors_objs, $limit ) {
718	global $wpdb;
719
720	$indexable_table = Model::get_table_name( 'Indexable' );
721
722	// This is a workaround for the fact that the array_column function does not work on objects in PHP 5.6.
723	$reassigned_authors_array = \array_map(
724	static function ( $obj ) {
725	return (array) $obj;
726	},
727	$reassigned_authors_objs,
728	);
729
730	$reassigned_authors = \array_combine( \array_column( $reassigned_authors_array, 'author_id' ), \array_column( $reassigned_authors_array, 'post_author' ) );
731
732	foreach ( $reassigned_authors as $old_author_id => $new_author_id ) {
733	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reason: There is no unescaped user input.
734	$query = $wpdb->prepare(
735	"
736	UPDATE {$indexable_table}
737	SET {$indexable_table}.author_id = {$new_author_id}
738	WHERE {$indexable_table}.author_id = {$old_author_id}
739	AND object_type='post'
740	LIMIT %d",
741	$limit,
742	);
743	// phpcs:enable
744
745	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reason: Already prepared.
746	$wpdb->query( $query );
747	}
748
749	return \count( $reassigned_authors );
750	}
751	}
752