thinkrank / includes / seo / class-snippet-index.php

class-snippet-index.php in ThinkRank AI SEO – AI SEO Plugin for WordPress: Schema, XML Sitemaps, Meta Tags, Search Console & Local SEO trunk, at includes/seo/class-snippet-index.php

1,069 lines 44.0 KB

Raw Download Zip

1	<?php
2	/**
3	* Persisted issue index for Bulk Snippets.
4	*
5	* @package ThinkRank\SEO
6	* @since 2.8.0
7	*/
8
9	declare(strict_types=1);
10
11	namespace ThinkRank\SEO;
12
13	// Prevent direct access
14	if (!defined('ABSPATH')) {
15	exit;
16	}
17
18	/**
19	* Snippet Index
20	*
21	* Bulk Snippets filters and counts by issue, and an issue depends on each
22	* post's rendered title and description — values that only exist once the
23	* post type's template is resolved. Resolving every post on every request
24	* made a page of 20 cost as much as the whole post type, which a store with
25	* 20,000 products cannot afford (#727 review).
26	*
27	* So each post's verdict is computed once and stored in one post meta value,
28	* `{generation}:{flags}:{title key}:{description key}`, and every list, count
29	* and page is answered by SQL over it:
30	*
31	* - flags is a bitmask of the issues that depend on the post alone.
32	* - title key is a hash of the title the page renders
33	* ({@see Snippet_Issues::duplicate_key()}), and description key a hash
34	* of the description it renders
35	* ({@see Snippet_Issues::description_duplicate_key()}). Duplicates are found
36	* at query time by grouping on them, so fixing one of two duplicates clears
37	* the other without touching the other's entry.
38	* - generation ties the entry to the global inputs (templates, robots
39	* settings, site name, separator). Changing any of them bumps the
40	* generation, which makes every entry stale at once without a write per post.
41	*
42	* Grouping is sitewide: two pages carrying the same title are competing
43	* with each other whether or not they are the same post type, which is how a
44	* post and a page built from one bad template collide (#564). Only entries at
45	* the current generation take part, so a post type the index has not reached
46	* yet cannot invent a duplicate — coverage growing can only reveal more.
47	*
48	* A post's own inputs (its title, content, SEO meta, robots meta, terms) mark
49	* just that post stale. Stale entries are rebuilt a bounded batch at a time by
50	* {@see self::refresh()}, so no request does unbounded work.
51	*
52	* @since 2.8.0
53	*/
54	class Snippet_Index {
55
56	/**
57	* Post meta holding the entry.
58	*/
59	public const META_KEY = '_thinkrank_snippet_index';
60
61	/**
62	* Option holding the current generation.
63	*/
64	public const GENERATION_OPTION = 'thinkrank_snippet_index_generation';
65
66	/**
67	* Option counting how many times entries have been rebuilt.
68	*
69	* The generation answers "did a global input change". This answers "did
70	* any entry change", which is what a report derived from the whole index
71	* has to know: one post's title edited to match another's changes the
72	* duplicate report without touching the generation. Bumped once per batch
73	* rather than once per entry, so a 500-post rebuild is one extra write.
74	*
75	* @since 2.10.0
76	*/
77	public const REVISION_OPTION = 'thinkrank_snippet_index_revision';
78
79	/**
80	* Version of the rules that turn a snapshot into an entry.
81	*
82	* Entries carry no record of how their keys were computed, so a change to
83	* those rules would leave every stored entry "current" with a key the new
84	* code would never produce: two posts indexed before a change and one
85	* after could not group. Raising this bumps the generation once on the
86	* next request, so everything is rebuilt under the new rules.
87	*
88	* - 2: keys compare entity-decoded text, and lengths are measured on it
89	* ({@see Snippet_Issues::duplicate_key()}).
90	*
91	* @since 2.10.0
92	*/
93	public const KEY_FORMAT = 2;
94
95	/**
96	* Option recording the {@see self::KEY_FORMAT} the stored entries follow.
97	*
98	* @since 2.10.0
99	*/
100	public const KEY_FORMAT_OPTION = 'thinkrank_snippet_index_key_format';
101
102	/**
103	* Taxonomy whose term names render into titles, through `%category%`.
104	*
105	* @since 2.10.0
106	*/
107	private const RENDERED_TAXONOMY = 'category';
108
109	/**
110	* Term names captured before an edit, keyed by term ID, so the edit can
111	* tell whether the name, the only thing a title renders, changed.
112	*
113	* @since 2.10.0
114	*
115	* @var array<int,string>
116	*/
117	private static $term_names_before = [];
118
119	/**
120	* Most entries one refresh call will build, and the time it may spend.
121	*/
122	private const REFRESH_MAX_POSTS = 500;
123	private const REFRESH_MAX_SECONDS = 2.0;
124
125	/**
126	* Post meta whose change changes a post's verdict.
127	*/
128	private const WATCHED_META = [
129	'_thinkrank_seo_title',
130	'_thinkrank_meta_description',
131	'_thinkrank_focus_keyword',
132	'_thinkrank_focus_keywords',
133	'_thinkrank_robots_meta',
134	'_thinkrank_robots_meta_enabled',
135	];
136
137	/**
138	* Options whose change changes every post's verdict.
139	*/
140	private const WATCHED_OPTIONS = [
141	'thinkrank_global_seo_settings',
142	'thinkrank_global_robot_meta_settings',
143	'blogname',
144	// %date% and %modified% render through get_the_date() in the site's
145	// date format, and in its language.
146	'date_format',
147	'WPLANG',
148	];
149
150	/**
151	* Register invalidation hooks. Runs on every request, not only on the
152	* Bulk Snippets screen, because edits happen everywhere else.
153	*
154	* @return void
155	*/
156	public function init(): void {
157	add_action('save_post', [self::class, 'mark_post_stale'], 99, 1);
158	add_action('set_object_terms', [self::class, 'mark_post_stale'], 10, 1);
159
160	add_action('added_post_meta', [self::class, 'on_meta_change'], 10, 3);
161	add_action('updated_post_meta', [self::class, 'on_meta_change'], 10, 3);
162	add_action('deleted_post_meta', [self::class, 'on_meta_change'], 10, 3);
163
164	add_action('updated_option', [self::class, 'on_option_change'], 10, 1);
165	add_action('added_option', [self::class, 'on_option_change'], 10, 1);
166
167	// Settings stored in ThinkRank's own table (site identity: separator,
168	// site name) do not go through update_option().
169	add_action('thinkrank_seo_settings_saved', [self::class, 'bump_generation'], 10, 0);
170
171	// %author% renders the display name.
172	add_action('profile_update', [self::class, 'bump_generation'], 10, 0);
173
174	// Deleting a user and attributing their posts to someone else
175	// rewrites post_author in one query, with no save_post for any post,
176	// so %author% changes on every one of them unseen.
177	add_action('deleted_user', [self::class, 'on_user_deleted'], 10, 2);
178
179	// %category% renders the first category's name, and renaming a
180	// category changes it on every post filed there without touching any
181	// of them. Assigning or removing a category already arrives through
182	// set_object_terms above, including the reassignment wp_delete_term()
183	// does.
184	add_action('edit_terms', [self::class, 'before_term_edit'], 10, 2);
185	add_action('edited_term', [self::class, 'after_term_edit'], 10, 3);
186
187	self::maybe_upgrade_key_format();
188	}
189
190	/**
191	* Rebuild everything once when the entry rules have changed since the
192	* stored entries were written. See {@see self::KEY_FORMAT}.
193	*
194	* The option is autoloaded, so the check on every request after the first
195	* is a read from memory.
196	*
197	* @since 2.10.0
198	*
199	* @return void
200	*/
201	public static function maybe_upgrade_key_format(): void {
202	if ((int) get_option(self::KEY_FORMAT_OPTION, 1) >= self::KEY_FORMAT) {
203	return;
204	}
205
206	self::bump_generation();
207	update_option(self::KEY_FORMAT_OPTION, self::KEY_FORMAT, true);
208	}
209
210	/**
211	* A user was deleted.
212	*
213	* @since 2.10.0
214	*
215	* @param int\|mixed $user_id Deleted user.
216	* @param int\|null\|mixed $reassign User their posts went to, or null when
217	* the posts were deleted with them.
218	* @return void
219	*/
220	public static function on_user_deleted($user_id, $reassign = null): void {
221	// Without a reassignment the posts were deleted, which drops them
222	// from every scope on its own.
223	if (null !== $reassign && (int) $reassign > 0) {
224	self::bump_generation();
225	}
226	}
227
228	/**
229	* Remember a category's name before it is edited.
230	*
231	* @since 2.10.0
232	*
233	* @param int\|mixed $term_id Term ID.
234	* @param string\|mixed $taxonomy Taxonomy.
235	* @return void
236	*/
237	public static function before_term_edit($term_id, $taxonomy): void {
238	if (self::RENDERED_TAXONOMY !== $taxonomy) {
239	return;
240	}
241
242	$term = get_term((int) $term_id, self::RENDERED_TAXONOMY);
243	if ($term instanceof \WP_Term) {
244	self::$term_names_before[(int) $term_id] = (string) $term->name;
245	}
246	}
247
248	/**
249	* A term was edited: rebuild everything when a category's name changed.
250	*
251	* Only the name renders, so a description, slug or parent edit is left
252	* alone; a rename is rare, and when it happens the posts filed under it
253	* are exactly the ones whose titles moved. Marking just those would mean a
254	* write per post in a category that may hold thousands, where the
255	* generation is one write and the rebuild is already bounded per request.
256	* Without a captured name, it is treated as renamed: a spurious rebuild
257	* costs time, a missed one reports stale duplicates.
258	*
259	* @since 2.10.0
260	*
261	* @param int\|mixed $term_id Term ID.
262	* @param int\|mixed $tt_id Term taxonomy ID.
263	* @param string\|mixed $taxonomy Taxonomy.
264	* @return void
265	*/
266	public static function after_term_edit($term_id, $tt_id, $taxonomy): void {
267	if (self::RENDERED_TAXONOMY !== $taxonomy) {
268	return;
269	}
270
271	$term_id = (int) $term_id;
272	$before = self::$term_names_before[$term_id] ?? null;
273	unset(self::$term_names_before[$term_id]);
274
275	$term = get_term($term_id, self::RENDERED_TAXONOMY);
276	if (null !== $before && $term instanceof \WP_Term && (string) $term->name === $before) {
277	return;
278	}
279
280	self::bump_generation();
281	}
282
283	/**
284	* Current generation.
285	*
286	* @return int
287	*/
288	public static function generation(): int {
289	return max(1, (int) get_option(self::GENERATION_OPTION, 1));
290	}
291
292	/**
293	* Make every entry stale at once.
294	*
295	* @return void
296	*/
297	public static function bump_generation(): void {
298	update_option(self::GENERATION_OPTION, self::generation() + 1, true);
299	}
300
301	/**
302	* How many times a batch of entries has been rebuilt.
303	*
304	* @since 2.10.0
305	*
306	* @return int
307	*/
308	public static function revision(): int {
309	return (int) get_option(self::REVISION_OPTION, 0);
310	}
311
312	/**
313	* Record that entries changed. Not autoloaded: only a report that spans
314	* the whole index reads it, and never on a front-end request.
315	*
316	* @since 2.10.0
317	*
318	* @return void
319	*/
320	private static function bump_revision(): void {
321	update_option(self::REVISION_OPTION, self::revision() + 1, false);
322	}
323
324	/**
325	* Make one post's entry stale.
326	*
327	* @param int\|mixed $post_id Post ID.
328	* @return void
329	*/
330	public static function mark_post_stale($post_id): void {
331	$post_id = (int) $post_id;
332	if ($post_id <= 0 \|\| wp_is_post_revision($post_id) \|\| wp_is_post_autosave($post_id)) {
333	return;
334	}
335
336	delete_post_meta($post_id, self::META_KEY);
337	}
338
339	/**
340	* Post meta changed.
341	*
342	* @param int\|array $meta_id Meta ID(s).
343	* @param int $object_id Post ID.
344	* @param string $meta_key Meta key.
345	* @return void
346	*/
347	public static function on_meta_change($meta_id, $object_id, $meta_key): void {
348	// Our own entry is written here too; reacting to it would delete what
349	// was just built.
350	if (in_array($meta_key, self::WATCHED_META, true)) {
351	self::mark_post_stale($object_id);
352	}
353	}
354
355	/**
356	* Option changed.
357	*
358	* @param string $option Option name.
359	* @return void
360	*/
361	public static function on_option_change($option): void {
362	if (in_array($option, self::WATCHED_OPTIONS, true)) {
363	self::bump_generation();
364	}
365	}
366
367	/**
368	* Encode an entry.
369	*
370	* @since 2.10.0 Carries a description key as well as a title key.
371	*
372	* @param int $generation Generation it was built under.
373	* @param int $flags Bitmask from {@see Snippet_Issues::to_flags()}.
374	* @param string $title_key Title duplicate key ('' when there is nothing to compare).
375	* @param string $description_key Description duplicate key ('' when there is nothing to compare).
376	* @return string
377	*/
378	public static function encode(int $generation, int $flags, string $title_key, string $description_key): string {
379	return $generation . ':' . $flags
380	. ':' . ('' === $title_key ? '-' : md5($title_key))
381	. ':' . ('' === $description_key ? '-' : md5($description_key));
382	}
383
384	/**
385	* Decode an entry.
386	*
387	* Entries written before 2.10.0 have three fields rather than four. They
388	* are rejected here and, because {@see self::current_like()} matches on the
389	* field count too, they read as stale and are rebuilt — so no upgrade
390	* routine is needed to migrate the format.
391	*
392	* @param string $value Stored value.
393	* @return array{generation:int, flags:int, title:string, description:string}\|null Null when malformed.
394	*/
395	public static function decode(string $value): ?array {
396	$parts = explode(':', $value);
397	if (4 !== count($parts) \|\| !ctype_digit($parts[0]) \|\| !ctype_digit($parts[1])) {
398	return null;
399	}
400
401	return [
402	'generation' => (int) $parts[0],
403	'flags' => (int) $parts[1],
404	'title' => '-' === $parts[2] ? '' : $parts[2],
405	'description' => '-' === $parts[3] ? '' : $parts[3],
406	];
407	}
408
409	/**
410	* LIKE pattern matching an entry that is current — right generation and
411	* right format.
412	*
413	* The three trailing wildcards mean three colons after the generation, so
414	* a pre-2.10.0 three-field entry does not match even at the current
415	* generation. That is the whole migration: it reads as stale.
416	*
417	* @since 2.10.0
418	*
419	* @return string
420	*/
421	private static function current_like(): string {
422	global $wpdb;
423
424	return $wpdb->esc_like(self::generation() . ':') . '%:%:%';
425	}
426
427	/**
428	* Build a bounded batch of stale entries for one post type.
429	*
430	* @param string $post_type Post type.
431	* @param string[] $statuses Post statuses.
432	* @return int How many stale entries remain after this batch.
433	*/
434	public static function refresh(string $post_type, array $statuses): int {
435	return self::refresh_scope(self::scope_sql([$post_type], $statuses, 'p'));
436	}
437
438	/**
439	* Build a bounded batch of stale entries across every post type given.
440	*
441	* The duplicate report is sitewide, so it needs entries for post types the
442	* Bulk Snippets screen may never have been opened on. Same batch bounds as
443	* {@see self::refresh()} — the caller keeps asking until it returns zero.
444	*
445	* @since 2.10.0
446	*
447	* @param string[] $post_types Post types.
448	* @param string[] $statuses Post statuses.
449	* @return int How many stale entries remain after this batch.
450	*/
451	public static function refresh_sitewide(array $post_types, array $statuses): int {
452	if (empty($post_types)) {
453	return 0;
454	}
455
456	return self::refresh_scope(self::scope_sql($post_types, $statuses, 'p'));
457	}
458
459	/**
460	* Build a bounded batch of the stale entries a scope covers.
461	*
462	* @param string $scope_sql Trusted WHERE fragment from {@see self::scope_sql()}.
463	* @return int How many stale entries remain after this batch.
464	*/
465	private static function refresh_scope(string $scope_sql): int {
466	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- interpolated parts are this class's own table aliases (whitelisted in alias()) and fragments already passed through prepare(); every value is a placeholder. The index is itself the cache.
467	global $wpdb;
468
469	$generation = self::generation();
470	$started = microtime(true);
471	$ids = $wpdb->get_col($wpdb->prepare(
472	"SELECT p.ID FROM {$wpdb->posts} p
473	LEFT JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = %s
474	WHERE " . $scope_sql . "
475	AND (m.meta_value IS NULL OR m.meta_value NOT LIKE %s)
476	ORDER BY p.ID DESC
477	LIMIT %d",
478	self::META_KEY,
479	self::current_like(),
480	self::REFRESH_MAX_POSTS
481	));
482
483	foreach (array_chunk(array_map('intval', $ids), 100) as $chunk) {
484	_prime_post_caches($chunk, false, true);
485
486	foreach ($chunk as $post_id) {
487	self::build($post_id, $generation);
488	}
489
490	if (microtime(true) - $started > self::REFRESH_MAX_SECONDS) {
491	break;
492	}
493	}
494
495	if (!empty($ids)) {
496	self::bump_revision();
497	}
498
499	return max(0, self::stale_count_in($scope_sql));
500	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
501	}
502
503	/**
504	* Build entries for specific posts now — used right after a save, so the
505	* response can report the saved rows' new state without a list refresh.
506	*
507	* @param int[] $post_ids Post IDs.
508	* @return void
509	*/
510	public static function rebuild(array $post_ids): void {
511	$generation = self::generation();
512	$post_ids = array_values(array_filter(array_map('intval', $post_ids)));
513	if (empty($post_ids)) {
514	return;
515	}
516
517	_prime_post_caches($post_ids, false, true);
518	foreach ($post_ids as $post_id) {
519	self::build($post_id, $generation);
520	}
521
522	self::bump_revision();
523	}
524
525	/**
526	* Compute and store one entry.
527	*
528	* @param int $post_id Post ID.
529	* @param int $generation Generation to stamp.
530	* @return void
531	*/
532	private static function build(int $post_id, int $generation): void {
533	$post = get_post($post_id);
534	if (!$post instanceof \WP_Post) {
535	return;
536	}
537
538	$snapshot = Snippet_Issues::snapshot($post);
539	$flags = Snippet_Issues::to_flags(Snippet_Issues::evaluate($snapshot));
540
541	update_post_meta($post_id, self::META_KEY, self::encode(
542	$generation,
543	$flags,
544	Snippet_Issues::duplicate_key((string) $snapshot['effective_title']),
545	Snippet_Issues::description_duplicate_key((string) $snapshot['effective_description'])
546	));
547	}
548
549	/**
550	* How many entries for one post type are missing or stale.
551	*
552	* @param string $post_type Post type.
553	* @param string[] $statuses Post statuses.
554	* @return int
555	*/
556	public static function stale_count(string $post_type, array $statuses): int {
557	return self::stale_count_in(self::scope_sql([$post_type], $statuses, 'p'));
558	}
559
560	/**
561	* How many entries the given scope is missing or has stale.
562	*
563	* @param string $scope_sql Trusted WHERE fragment from {@see self::scope_sql()}.
564	* @return int
565	*/
566	private static function stale_count_in(string $scope_sql): int {
567	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- interpolated parts are this class's own table aliases (whitelisted in alias()) and fragments already passed through prepare(); every value is a placeholder. The index is itself the cache.
568	global $wpdb;
569	return (int) $wpdb->get_var($wpdb->prepare(
570	"SELECT COUNT(*) FROM {$wpdb->posts} p
571	LEFT JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = %s
572	WHERE " . $scope_sql . "
573	AND (m.meta_value IS NULL OR m.meta_value NOT LIKE %s)",
574	self::META_KEY,
575	self::current_like()
576	));
577	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
578	}
579
580	/**
581	* Counts and one page of post IDs, answered from the index.
582	*
583	* Only current entries are considered; while {@see self::refresh()} still
584	* has stale ones, counts are partial and the caller says so.
585	*
586	* @param array{
587	* post_type: string,
588	* statuses: string[],
589	* group_post_types: string[],
590	* issue: string,
591	* search: string,
592	* page: int,
593	* per_page: int,
594	* visibility_sql: string
595	* } $args Query arguments. visibility_sql is a trusted WHERE fragment from
596	* {@see self::visibility_sql()} ('' for no restriction), and
597	* group_post_types are the post types duplicates are looked for
598	* across ({@see Global_SEO_Post_Types::allowed()}).
599	* @return array{ids: int[], total: int, counts: array<string,int>, with_problem: int, duplicate_ids: int[], duplicate_description_ids: int[]}
600	*/
601	public static function query(array $args): array {
602	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- interpolated parts are this class's own table aliases (whitelisted in alias()) and fragments already passed through prepare(); every value is a placeholder. The index is itself the cache.
603	global $wpdb;
604
605	$current = self::current_like();
606
607	$flags_sql = "CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(m.meta_value, ':', 2), ':', -1) AS UNSIGNED)";
608	$title_key_sql = self::key_sql('m', 'title');
609	$description_key_sql = self::key_sql('m', 'description');
610
611	// Duplicates are about the site, not about what this user may see: a
612	// title another post already uses is a problem whether or not the
613	// viewer can open that post. Only the flag is revealed, never the peer.
614	//
615	// The scope is every post type ThinkRank manages, not the one being
616	// listed: a page and a post carrying the same title compete with each
617	// other (#564). Post types whose entries are still stale simply do not
618	// take part yet, which can hide a duplicate but never invent one.
619	$group_types = self::group_post_types($args);
620	$title_dupes_sql = self::duplicate_group_sql($group_types, $args['statuses'], 'title');
621	$description_dupes_sql = self::duplicate_group_sql($group_types, $args['statuses'], 'description');
622
623	$where = self::scope_sql([$args['post_type']], $args['statuses'], 'p')
624	. $wpdb->prepare(' AND m.meta_value LIKE %s', $current);
625
626	if ('' !== $args['visibility_sql']) {
627	$where .= ' AND (' . $args['visibility_sql'] . ')';
628	}
629
630	if ('' !== $args['search']) {
631	$like = '%' . $wpdb->esc_like($args['search']) . '%';
632	$where .= $wpdb->prepare(' AND (p.post_title LIKE %s OR p.post_content LIKE %s)', $like, $like);
633	}
634
635	$from = "{$wpdb->posts} p
636	INNER JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = '" . esc_sql(self::META_KEY) . "'
637	LEFT JOIN ({$title_dupes_sql}) d ON d.dk = {$title_key_sql}
638	LEFT JOIN ({$description_dupes_sql}) e ON e.dk = {$description_key_sql}";
639
640	// One pass for every chip count.
641	$select = [
642	'COUNT(*) AS total',
643	"SUM(({$flags_sql}) > 0 OR d.dk IS NOT NULL OR e.dk IS NOT NULL) AS with_problem",
644	'SUM(d.dk IS NOT NULL) AS ' . Snippet_Issues::DUPLICATE_TITLE,
645	'SUM(e.dk IS NOT NULL) AS ' . Snippet_Issues::DUPLICATE_DESCRIPTION,
646	];
647	foreach (Snippet_Issues::flag_bits() as $issue => $bit) {
648	$select[] = "SUM(({$flags_sql} & {$bit}) > 0) AS {$issue}";
649	}
650	$row = (array) $wpdb->get_row('SELECT ' . implode(', ', $select) . " FROM {$from} WHERE {$where}", ARRAY_A);
651
652	$counts = [];
653	foreach (Snippet_Issues::all() as $issue) {
654	$counts[$issue] = (int) ($row[$issue] ?? 0);
655	}
656
657	$issue_sql = '';
658	if (Snippet_Issues::DUPLICATE_TITLE === $args['issue']) {
659	$issue_sql = ' AND d.dk IS NOT NULL';
660	} elseif (Snippet_Issues::DUPLICATE_DESCRIPTION === $args['issue']) {
661	$issue_sql = ' AND e.dk IS NOT NULL';
662	} elseif ('' !== $args['issue']) {
663	$bits = Snippet_Issues::flag_bits();
664	$issue_sql = ' AND (' . $flags_sql . ' & ' . (int) $bits[$args['issue']] . ') > 0';
665	}
666
667	$total = '' === $args['issue'] ? (int) ($row['total'] ?? 0) : $counts[$args['issue']];
668	$offset = max(0, ($args['page'] - 1) * $args['per_page']);
669	$page = $wpdb->get_results($wpdb->prepare(
670	"SELECT p.ID, (d.dk IS NOT NULL) AS dup, (e.dk IS NOT NULL) AS dup_description
671	FROM {$from} WHERE {$where}{$issue_sql}
672	ORDER BY p.post_date DESC, p.ID DESC LIMIT %d OFFSET %d",
673	$args['per_page'],
674	$offset
675	), ARRAY_A);
676
677	$ids = [];
678	$duplicate_ids = [];
679	$duplicate_description_ids = [];
680	foreach ((array) $page as $item) {
681	$ids[] = (int) $item['ID'];
682	if (!empty($item['dup'])) {
683	$duplicate_ids[] = (int) $item['ID'];
684	}
685	if (!empty($item['dup_description'])) {
686	$duplicate_description_ids[] = (int) $item['ID'];
687	}
688	}
689
690	return [
691	'ids' => $ids,
692	'total' => $total,
693	// Before the issue filter, so the "All" chip keeps its number.
694	'total_all' => (int) ($row['total'] ?? 0),
695	'counts' => $counts,
696	'with_problem' => (int) ($row['with_problem'] ?? 0),
697	'duplicate_ids' => $duplicate_ids,
698	'duplicate_description_ids' => $duplicate_description_ids,
699	];
700	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
701	}
702
703	/**
704	* Which of these posts share their title, or their description, with
705	* another post on the site.
706	*
707	* @since 2.10.0 Reports descriptions too, and groups across post types.
708	*
709	* @param string[] $post_types Post types duplicates are looked for across.
710	* @param string[] $statuses Post statuses.
711	* @param int[] $post_ids Posts to check.
712	* @return array{title:int[], description:int[]} The ones that are duplicates.
713	*/
714	public static function duplicates_among(array $post_types, array $statuses, array $post_ids): array {
715	$post_ids = array_values(array_filter(array_map('intval', $post_ids)));
716	if (empty($post_ids) \|\| empty($post_types)) {
717	return ['title' => [], 'description' => []];
718	}
719
720	return [
721	'title' => self::duplicates_of($post_types, $statuses, $post_ids, 'title'),
722	'description' => self::duplicates_of($post_types, $statuses, $post_ids, 'description'),
723	];
724	}
725
726	/**
727	* Which of these posts share one kind of key with another post on the site.
728	*
729	* @param string[] $post_types Post types duplicates are looked for across.
730	* @param string[] $statuses Post statuses.
731	* @param int[] $post_ids Posts to check (already integers, non-empty).
732	* @param string $which 'title' or 'description'.
733	* @return int[]
734	*/
735	private static function duplicates_of(array $post_types, array $statuses, array $post_ids, string $which): array {
736	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- interpolated parts are this class's own table aliases (whitelisted in alias()) and fragments already passed through prepare(); every value is a placeholder. The index is itself the cache.
737	global $wpdb;
738
739	$key_sql = self::key_sql('m', $which);
740	$in = implode(',', $post_ids);
741	$groups = self::duplicate_group_sql($post_types, $statuses, $which);
742
743	$rows = $wpdb->get_col($wpdb->prepare(
744	"SELECT m.post_id FROM {$wpdb->postmeta} m
745	WHERE m.meta_key = %s AND m.post_id IN ({$in}) AND m.meta_value LIKE %s
746	AND {$key_sql} <> '-'
747	AND {$key_sql} IN (SELECT dk FROM ({$groups}) g)",
748	self::META_KEY,
749	self::current_like()
750	));
751
752	return array_map('intval', (array) $rows);
753	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
754	}
755
756	/**
757	* How many posts in scope have a current entry — the size of what the
758	* duplicate report actually looked at, as opposed to what it will cover
759	* once {@see self::refresh_sitewide()} has finished.
760	*
761	* @since 2.10.0
762	*
763	* @param string[] $post_types Post types.
764	* @param string[] $statuses Post statuses.
765	* @return int
766	*/
767	public static function current_count(array $post_types, array $statuses): int {
768	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- interpolated parts are this class's own table aliases (whitelisted in alias()) and fragments already passed through prepare(); every value is a placeholder. The index is itself the cache.
769	global $wpdb;
770
771	if (empty($post_types)) {
772	return 0;
773	}
774
775	return (int) $wpdb->get_var($wpdb->prepare(
776	"SELECT COUNT(*) FROM {$wpdb->posts} p
777	INNER JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = %s
778	WHERE " . self::scope_sql($post_types, $statuses, 'p') . "
779	AND m.meta_value LIKE %s",
780	self::META_KEY,
781	self::current_like()
782	));
783	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
784	}
785
786	/**
787	* How many duplicate groups there are, and how many posts they hold.
788	*
789	* Separate from {@see self::duplicate_groups()} because the report lists a
790	* bounded number of groups but must state the true totals above them — a
791	* count taken from the listed groups would under-report the moment the
792	* list is capped.
793	*
794	* @since 2.10.0
795	*
796	* @param string[] $post_types Post types to group across.
797	* @param string[] $statuses Post statuses.
798	* @param string $which 'title' or 'description'.
799	* @return array{groups:int, posts:int}
800	*/
801	public static function duplicate_totals(array $post_types, array $statuses, string $which): array {
802	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- interpolated parts are this class's own table aliases (whitelisted in alias()) and fragments already passed through prepare(); every value is a placeholder. The index is itself the cache.
803	global $wpdb;
804
805	if (empty($post_types)) {
806	return ['groups' => 0, 'posts' => 0];
807	}
808
809	$groups_sql = self::duplicate_group_sql($post_types, $statuses, $which, true);
810
811	$row = (array) $wpdb->get_row(
812	"SELECT COUNT(*) AS groups_count, COALESCE(SUM(g.members), 0) AS posts_count
813	FROM ({$groups_sql}) g",
814	ARRAY_A
815	);
816
817	return [
818	'groups' => (int) ($row['groups_count'] ?? 0),
819	'posts' => (int) ($row['posts_count'] ?? 0),
820	];
821	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
822	}
823
824	/**
825	* Every key more than one post in scope shares, with the posts carrying it.
826	*
827	* Largest group first, so a template stamping one title on fifty pages is
828	* the first thing a reader sees rather than something they page to.
829	*
830	* The member IDs come back through GROUP_CONCAT so this stays one query
831	* rather than one per group. `total` is a real COUNT and is exact even
832	* when the concatenated list was cut short by `group_concat_max_len`,
833	* which is why the caller reports the count and the names separately.
834	*
835	* @since 2.10.0
836	*
837	* @param string[] $post_types Post types to group across.
838	* @param string[] $statuses Post statuses.
839	* @param string $which 'title' or 'description'.
840	* @param int $max_members Most member IDs to return per group.
841	* @param int $max_groups Most groups to return; 0 for all.
842	* @return array<int,array{key:string, post_ids:int[], total:int}>
843	*/
844	public static function duplicate_groups(
845	array $post_types,
846	array $statuses,
847	string $which,
848	int $max_members,
849	int $max_groups = 0
850	): array {
851	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- interpolated parts are this class's own table aliases (whitelisted in alias()) and fragments already passed through prepare(); every value is a placeholder. The index is itself the cache.
852	global $wpdb;
853
854	if (empty($post_types)) {
855	return [];
856	}
857
858	$posts_alias = 'title' === $which ? 'dp' : 'ep';
859	$meta_alias = 'title' === $which ? 'dm' : 'em';
860	$key_sql = self::key_sql($meta_alias, $which);
861
862	$limit_sql = $max_groups > 0 ? $wpdb->prepare(' LIMIT %d', $max_groups) : '';
863
864	$rows = $wpdb->get_results($wpdb->prepare(
865	"SELECT {$key_sql} AS dk,
866	COUNT(*) AS total,
867	GROUP_CONCAT({$posts_alias}.ID ORDER BY {$posts_alias}.post_date DESC) AS ids
868	FROM {$wpdb->posts} {$posts_alias}
869	INNER JOIN {$wpdb->postmeta} {$meta_alias}
870	ON {$meta_alias}.post_id = {$posts_alias}.ID AND {$meta_alias}.meta_key = %s
871	WHERE " . self::scope_sql($post_types, $statuses, $posts_alias) . "
872	AND {$meta_alias}.meta_value LIKE %s
873	GROUP BY dk
874	HAVING COUNT(*) > 1 AND dk <> '-'
875	ORDER BY total DESC, dk ASC" . $limit_sql,
876	self::META_KEY,
877	self::current_like()
878	), ARRAY_A);
879
880	$groups = [];
881	foreach ((array) $rows as $row) {
882	$ids = array_values(array_filter(array_map('intval', explode(',', (string) $row['ids']))));
883
884	$groups[] = [
885	'key' => (string) $row['dk'],
886	'total' => (int) $row['total'],
887	'post_ids' => $max_members > 0 ? array_slice($ids, 0, $max_members) : $ids,
888	];
889	}
890
891	return $groups;
892	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
893	}
894
895	/**
896	* The keys shared by more than one post, as a subquery selecting `dk`.
897	*
898	* Each kind gets its own table aliases so two of these can appear in one
899	* statement.
900	*
901	* @since 2.10.0
902	*
903	* @param string[] $post_types Post types to group across.
904	* @param string[] $statuses Post statuses.
905	* @param string $which 'title' or 'description'.
906	* @param bool $with_counts Also select the group's size as `members`.
907	* @return string Trusted SQL.
908	*/
909	private static function duplicate_group_sql(array $post_types, array $statuses, string $which, bool $with_counts = false): string {
910	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- interpolated parts are this class's own table aliases (whitelisted in alias()) and fragments already passed through prepare(); every value is a placeholder. The index is itself the cache.
911	global $wpdb;
912
913	$posts_alias = 'title' === $which ? 'dp' : 'ep';
914	$meta_alias = 'title' === $which ? 'dm' : 'em';
915	$key_sql = self::key_sql($meta_alias, $which);
916	$members_sql = $with_counts ? ', COUNT(*) AS members' : '';
917
918	return $wpdb->prepare(
919	"SELECT {$key_sql} AS dk{$members_sql}
920	FROM {$wpdb->posts} {$posts_alias}
921	INNER JOIN {$wpdb->postmeta} {$meta_alias}
922	ON {$meta_alias}.post_id = {$posts_alias}.ID AND {$meta_alias}.meta_key = %s
923	WHERE " . self::scope_sql($post_types, $statuses, $posts_alias) . "
924	AND {$meta_alias}.meta_value LIKE %s
925	GROUP BY dk
926	HAVING COUNT(*) > 1 AND dk <> '-'",
927	self::META_KEY,
928	self::current_like()
929	);
930	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
931	}
932
933	/**
934	* SQL reading one key out of a stored entry.
935	*
936	* The title key is the third colon-separated field and the description key
937	* the fourth, which is also the last.
938	*
939	* @since 2.10.0
940	*
941	* @param string $alias Postmeta table alias.
942	* @param string $which 'title' or 'description'.
943	* @return string Trusted SQL.
944	*/
945	private static function key_sql(string $alias, string $which): string {
946	$alias = self::alias($alias);
947
948	return 'title' === $which
949	? "SUBSTRING_INDEX(SUBSTRING_INDEX({$alias}.meta_value, ':', 3), ':', -1)"
950	: "SUBSTRING_INDEX({$alias}.meta_value, ':', -1)";
951	}
952
953	/**
954	* The post types a query groups duplicates across.
955	*
956	* Falls back to the type being listed, so a caller that has not said
957	* behaves as the pre-#564 per-type grouping rather than silently grouping
958	* over nothing.
959	*
960	* @since 2.10.0
961	*
962	* @param array<string,mixed> $args Query arguments.
963	* @return string[]
964	*/
965	private static function group_post_types(array $args): array {
966	$types = array_values(array_filter((array) ($args['group_post_types'] ?? []), 'is_string'));
967
968	return empty($types) ? [(string) $args['post_type']] : $types;
969	}
970
971	/**
972	* WHERE fragment limiting posts to what the current user may read.
973	*
974	* The SQL form of map_meta_cap('read_post') for the statuses this screen
975	* lists, so pagination and counts are computed over readable posts only —
976	* filtering after the LIMIT would leave short pages and counts that
977	* include posts the user cannot see:
978	*
979	* - published: readable by anyone who can reach the screen;
980	* - private: the post type's read_private_posts, or the author;
981	* - draft, pending, scheduled: the post type's edit_others_posts, or the author.
982	*
983	* @param string $post_type Post type.
984	* @param string $alias Posts table alias.
985	* @return string Trusted SQL, '' when the user may read everything.
986	*/
987	public static function visibility_sql(string $post_type, string $alias = 'p'): string {
988	$alias = self::alias($alias);
989	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- interpolated parts are this class's own table aliases (whitelisted in alias()) and fragments already passed through prepare(); every value is a placeholder. The index is itself the cache.
990	global $wpdb;
991
992	$object = get_post_type_object($post_type);
993	if (!$object instanceof \WP_Post_Type) {
994	return '0 = 1';
995	}
996
997	$can_private = current_user_can($object->cap->read_private_posts);
998	$can_others = current_user_can($object->cap->edit_others_posts);
999
1000	if ($can_private && $can_others) {
1001	return '';
1002	}
1003
1004	$author = (int) get_current_user_id();
1005	$clauses = ["{$alias}.post_status = 'publish'"];
1006
1007	$clauses[] = $can_private
1008	? "{$alias}.post_status = 'private'"
1009	: $wpdb->prepare("({$alias}.post_status = 'private' AND {$alias}.post_author = %d)", $author);
1010
1011	$clauses[] = $can_others
1012	? "{$alias}.post_status IN ('draft','pending','future')"
1013	: $wpdb->prepare("({$alias}.post_status IN ('draft','pending','future') AND {$alias}.post_author = %d)", $author);
1014
1015	return implode(' OR ', $clauses);
1016	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1017	}
1018
1019	/**
1020	* WHERE fragment for post types and statuses.
1021	*
1022	* @since 2.10.0 Takes a list of post types rather than one.
1023	*
1024	* @param string[] $post_types Post types.
1025	* @param string[] $statuses Post statuses.
1026	* @param string $alias Posts table alias.
1027	* @return string
1028	*/
1029	private static function scope_sql(array $post_types, array $statuses, string $alias): string {
1030	$alias = self::alias($alias);
1031	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the alias is whitelisted in alias(); both IN lists are runs of %s built from the argument counts, so the sniff cannot see the placeholders it is looking for, and every value is still passed to prepare().
1032	global $wpdb;
1033
1034	$post_types = array_values(array_unique(array_filter((array) $post_types, 'is_string')));
1035	if (empty($post_types)) {
1036	// No post type matches nothing. Falling back to every post type
1037	// here would silently widen a scope the caller meant to narrow.
1038	return '1 = 0';
1039	}
1040
1041	$statuses = array_values(array_intersect($statuses, ['publish', 'future', 'draft', 'pending', 'private']));
1042	if (empty($statuses)) {
1043	$statuses = ['publish'];
1044	}
1045
1046	$types_in = implode(',', array_fill(0, count($post_types), '%s'));
1047	$statuses_in = implode(',', array_fill(0, count($statuses), '%s'));
1048
1049	return $wpdb->prepare(
1050	"{$alias}.post_type IN ({$types_in}) AND {$alias}.post_status IN ({$statuses_in})",
1051	array_merge($post_types, $statuses)
1052	);
1053	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1054	}
1055
1056	/**
1057	* A table alias this class uses, and nothing else.
1058	*
1059	* Aliases are interpolated into SQL (identifiers cannot be placeholders),
1060	* so only the fixed set this class writes is accepted.
1061	*
1062	* @param string $alias Requested alias.
1063	* @return string
1064	*/
1065	private static function alias(string $alias): string {
1066	return in_array($alias, ['p', 'm', 'dp', 'dm', 'ep', 'em'], true) ? $alias : 'p';
1067	}
1068	}
1069