internal-links / statistics / link.php

link.php in Internal Link Juicer: SEO Auto Linker for WordPress trunk, at statistics/link.php

449 lines 14.0 KB

Raw Download Zip

1	<?php
2	namespace ILJ\Statistics;
3
4	use ILJ\Data\Content;
5	use ILJ\Database\Linkindex;
6
7	/**
8	* Gets Link statistics
9	*
10	* Gets the link statistics for the dashboard
11	*
12	* @package ILJ\Statistics
13	* @since 2.23.5
14	*/
15	class Link {
16
17	/**
18	* Arguments for statistics query.
19	*
20	* @var array $args {
21	* @type int $limit The number of rows which needs to be returned.
22	* @type int $offset The number of rows which needs to be offset (used for pagination).
23	* @type string $sort_by The field which needs to be used for sorting, one of 'title', 'keywords_count', 'incoming_links', 'outgoing_links'
24	* @type string $sort_direction The sort direction, it can be ASC or DESC
25	* @type string $search The search query (optional)
26	* @type array $main_types The main types filter (optional)
27	* @type array $sub_types The sub types filter (optional)
28	* }
29	*/
30	private $args;
31
32	/**
33	* Constructor for {@link Link} class
34	*
35	* @param array $args {
36	* @type int $limit The number of rows which needs to be returned.
37	* @type int $offset The number of rows which needs to be offset (used for pagination).
38	* @type string $sort_by The field which needs to be used for sorting, one of 'title', 'keywords_count', 'incoming_links', 'outgoing_links'
39	* @type string $sort_direction The sort direction, it can be ASC or DESC
40	* @type string $search The search query (optional)
41	* @type array $main_types The main types filter (optional)
42	* @type array $sub_types The sub types filter (optional)
43	* }
44	*/
45	public function __construct($args) {
46	$this->args = wp_parse_args($args, array(
47	'sort_by' => 'title',
48	'sort_direction' => 'ASC',
49	'limit' => 10,
50	'offset' => 0,
51	'search' => '',
52	'types' => array('post', 'term'),
53	'main_types' => array(),
54	'sub_types' => array(),
55	));
56	}
57
58	private function should_apply_main_types_filter() {
59	return !empty($this->args['main_types']);
60	}
61
62	private function should_apply_sub_types_filter() {
63	return !empty($this->args['sub_types']);
64	}
65
66	private function get_sql_escaped_main_types() {
67	return sprintf("'%s'", implode("','", array_map('esc_sql', $this->args['main_types'])));
68	}
69	private function get_sql_escaped_sub_types() {
70	return sprintf("'%s'", implode("','", array_map('esc_sql', $this->args['sub_types'])));
71	}
72
73
74
75
76	/**
77	* Get sort_by after validation.
78	*
79	* @return string
80	*/
81	private function get_sort_by() {
82	$allowed_sorting_columns = array('title', 'keywords_count', 'incoming_links', 'outgoing_links');
83	return in_array($this->args['sort_by'], $allowed_sorting_columns, true) ? $this->args['sort_by'] : 'title';
84	}
85
86	/**
87	* Get sort direction after validation.
88	*
89	* @return string
90	*/
91	private function get_sort_direction() {
92	$allowed_sorting_directions = array('ASC', 'DESC');
93	return in_array($this->args['sort_direction'], $allowed_sorting_directions, true) ? $this->args['sort_direction'] : 'ASC';
94	}
95
96
97
98	/**
99	* Return the query for sub_type, since the link statistics is now paginated, these types
100	* needs to be available in the query for filtering, the equivalent function is
101	* {@link \ILJ\Helper\IndexAsset::getDetailedType}
102	*
103	* @return string
104	*/
105	private static function get_sub_type_query() {
106	$sub_type_query = "
107	CASE
108	WHEN idx.type = 'post' THEN items.entity_type
109	ELSE ''
110	END AS sub_type";
111
112
113	return $sub_type_query;
114	}
115
116	/**
117	* Return a map of main_type and sub_type which will be used for filtering in the
118	* link statistics ui screen.
119	*
120	* @return array
121	*/
122	public static function get_types() {
123	global $wpdb;
124
125	$link_index_table_name = $wpdb->prefix . Linkindex::ILJ_DATABASE_TABLE_LINKINDEX;
126	$sub_type_query = self::get_sub_type_query();
127
128	$term_query = "";
129	$type_condition_query = "WHERE idx.type = items.type AND (idx.type != CONCAT(items.type, '_meta') OR items.entity_type != 'ilj_customlinks' OR items.entity_type != 'term')";
130
131
132
133	$query = "
134	SELECT
135	idx.type AS main_type,
136	{$sub_type_query}
137	FROM
138	(
139	SELECT
140	p.ID AS id,
141	'post' AS type,
142	p.post_type as entity_type
143	FROM
144	$wpdb->posts p
145	LEFT JOIN $wpdb->postmeta pm ON p.ID = pm.post_id AND pm.meta_key = 'ilj_linkdefinition'
146	WHERE p.post_status = 'publish'
147	$term_query
148	) items
149
150	RIGHT JOIN (
151	SELECT DISTINCT link_from as id, type_from AS type FROM $link_index_table_name
152	UNION
153	SELECT DISTINCT link_to AS id, type_to AS type FROM $link_index_table_name
154	) AS idx ON items.id = idx.id
155	$type_condition_query
156	GROUP BY main_type, sub_type
157	";
158	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Direct query is necessary for real-time data fetch and caching is not applicable for this use case.
159	return $wpdb->get_results($query);
160	}
161
162	/**
163	* Returns the statistics for linkindex table.
164	*
165	* @return array
166	*/
167	public function get_statistics() {
168	global $wpdb;
169	$link_index_table_name = $wpdb->prefix . Linkindex::ILJ_DATABASE_TABLE_LINKINDEX;
170
171	$term_query = "";
172	$type_condition_query = "AND idx.type = items.type";
173
174
175	$query = "
176	SELECT * FROM (
177	SELECT
178	items.id, idx.type AS main_type, items.type, items.keywords_count, items.title,
179	COALESCE(incoming_links.count, 0) AS incoming_links,
180	COALESCE(outgoing_links.count, 0) AS outgoing_links,
181	{$this->get_sub_type_query()}
182	FROM
183	(
184	SELECT
185	p.ID AS id,
186	p.post_title AS title,
187	'post' AS type,
188	COALESCE(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(pm.meta_value, 'a:', -1), ':', 1) AS SIGNED), 0) AS keywords_count,
189	p.post_type as entity_type
190	FROM
191	$wpdb->posts p
192	LEFT JOIN $wpdb->postmeta pm ON p.ID = pm.post_id AND pm.meta_key = 'ilj_linkdefinition'
193	WHERE p.post_status = 'publish'
194	$term_query
195	) items
196
197	LEFT JOIN (
198	SELECT DISTINCT link_from AS id, type_from AS type FROM $link_index_table_name
199	UNION
200	SELECT DISTINCT link_to AS id, type_to AS type FROM $link_index_table_name
201	) AS idx ON items.id = idx.id $type_condition_query
202
203	LEFT JOIN (
204	SELECT link_to AS id, type_to AS TYPE, COUNT(1) AS count
205	FROM $link_index_table_name
206	GROUP BY link_to, type_to
207	) AS incoming_links ON items.id = incoming_links.id AND idx.type = incoming_links.type
208
209	LEFT JOIN (
210	SELECT link_from AS id, type_from AS TYPE, COUNT(1) AS count
211	FROM $link_index_table_name
212	GROUP BY link_from, type_from
213	) AS outgoing_links ON items.id = outgoing_links.id AND idx.type = outgoing_links.type
214	) AS results WHERE 1=1
215	";
216	if (!empty($this->args['search'])) {
217	$query .= " AND title LIKE %s";
218	}
219	if ($this->should_apply_main_types_filter() && $this->should_apply_sub_types_filter()) {
220	$query .= " AND (main_type IN ({$this->get_sql_escaped_main_types()}) AND sub_type IN ({$this->get_sql_escaped_sub_types()}) )";
221	}
222
223	$query .= " AND (incoming_links > 0 OR outgoing_links > 0)";
224	$query .= " ORDER BY {$this->get_sort_by()} {$this->get_sort_direction()} LIMIT %d OFFSET %d;";
225
226	if (!empty($this->args['search'])) {
227	$prepared_query = $wpdb->prepare(
228	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Already prepared.
229	$query,
230	!empty($this->args['search']) ? '%' . $wpdb->esc_like($this->args['search']) . '%' : '',
231	$this->args['limit'],
232	$this->args['offset']
233	);
234	} else {
235	$prepared_query = $wpdb->prepare(
236	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Already prepared
237	$query,
238	$this->args['limit'],
239	$this->args['offset']
240	);
241	}
242
243	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Direct query is necessary for real-time data fetch and caching is not applicable for this use case.
244	$results = $wpdb->get_results(
245	$prepared_query,
246	ARRAY_A
247	);
248	return array_map(function ($result) {
249	$content = Content::from_content_type_and_id($result['type'], $result['sub_type'], $result['id']);
250	$result['edit_link'] = $content->get_edit_link();
251	$result['edit_title'] = $content->get_edit_title();
252	$result['permalink'] = $content->get_permalink();
253	$result['permalink_title'] = $content->get_permalink_title();
254	return $result;
255	}, $results);
256	}
257
258	/**
259	* Return the total number of filtered rows.
260	*
261	* @return int
262	*/
263	public function get_filtered_results_count() {
264	global $wpdb;
265
266	$link_index_table_name = $wpdb->prefix . Linkindex::ILJ_DATABASE_TABLE_LINKINDEX;
267	$query = "
268	SELECT COUNT(1) FROM (
269	SELECT
270	idx.type as main_type,
271	items.title,
272	{$this->get_sub_type_query()}
273	FROM
274	(
275	SELECT
276	p.ID AS id,
277	'post' AS type,
278	p.post_type as entity_type,
279	p.post_title AS title
280	FROM
281	$wpdb->posts p
282	LEFT JOIN $wpdb->postmeta pm ON p.ID = pm.post_id AND pm.meta_key = 'ilj_linkdefinition'
283	WHERE p.post_status = 'publish'
284	UNION
285
286	SELECT
287	t.term_id AS id,
288	'term' AS type,
289	tt.taxonomy as entity_type,
290	t.name as title
291	FROM
292	$wpdb->terms t
293	LEFT JOIN $wpdb->term_taxonomy tt ON t.term_id = tt.term_id
294	) items
295
296	RIGHT JOIN (
297	SELECT
298	link_from, type_from AS type
299	FROM
300	$link_index_table_name
301	GROUP BY
302	link_from,
303	type_from
304	) AS idx ON items.id = idx.link_from AND (idx.type = items.type OR idx.type = CONCAT(items.type, '_meta'))
305
306	UNION
307
308	SELECT
309	idx.type as main_type,
310	items.title,
311	{$this->get_sub_type_query()}
312	FROM
313	(
314	SELECT
315	p.ID AS id,
316	'post' AS type,
317	p.post_type as entity_type,
318	p.post_title AS title
319	FROM
320	$wpdb->posts p
321	LEFT JOIN $wpdb->postmeta pm ON p.ID = pm.post_id AND pm.meta_key = 'ilj_linkdefinition'
322	WHERE p.post_status = 'publish'
323	UNION
324
325	SELECT
326	t.term_id AS id,
327	'term' AS type,
328	tt.taxonomy as entity_type,
329	t.name as title
330	FROM
331	$wpdb->terms t
332	LEFT JOIN $wpdb->term_taxonomy tt ON t.term_id = tt.term_id
333	) items
334
335	RIGHT JOIN (
336	SELECT
337	link_to, type_to AS type
338	FROM
339	$link_index_table_name
340	GROUP BY
341	link_to,
342	type_to
343	) AS idx ON items.id = idx.link_to AND (idx.type = items.type OR idx.type = CONCAT(items.type, '_meta'))) AS results WHERE 1=1";
344
345	if (!empty($this->args['search'])) {
346	$query .= " AND title LIKE %s";
347	}
348	if ($this->should_apply_main_types_filter() && $this->should_apply_sub_types_filter()) {
349	$query .= " AND (main_type IN ({$this->get_sql_escaped_main_types()}) AND sub_type IN ({$this->get_sql_escaped_sub_types()}) )";
350	}
351	if (!empty($this->args['search'])) {
352	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query is necessary for real-time data fetch and caching is not applicable for this use case.
353	return intval($wpdb->get_var($wpdb->prepare(
354	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Already prepared.
355	$query,
356	'%' . $wpdb->esc_like($this->args['search']) . '%'
357	)));
358	} else {
359	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query is necessary for real-time data fetch and caching is not applicable for this use case.
360	return intval($wpdb->get_var($query));
361	}
362	}
363
364	/**
365	* Return the total number of rows in link statistics.
366	*
367	* @return int
368	*/
369	public function get_total() {
370	global $wpdb;
371	$link_index_table_name = $wpdb->prefix . Linkindex::ILJ_DATABASE_TABLE_LINKINDEX;
372	$count_query = "
373	SELECT COUNT(DISTINCT id, type)
374	FROM (
375	SELECT
376	items.id,
377	CASE
378	WHEN idx.type = CONCAT(items.type, '_meta') THEN CONCAT(items.type, '_meta')
379	ELSE items.type
380	END AS type
381	FROM
382	(
383	SELECT
384	p.ID AS id,
385	'post' AS type
386	FROM
387	$wpdb->posts p
388	WHERE p.post_status = 'publish'
389	UNION
390	SELECT
391	t.term_id AS id,
392	'term' AS type
393	FROM
394	$wpdb->terms t
395	) items
396	RIGHT JOIN (
397	SELECT
398	link_from AS id,
399	type_from AS type
400	FROM
401	$link_index_table_name
402	GROUP BY
403	link_from, type_from
404	) AS idx
405	ON items.id = idx.id
406	AND (idx.type = items.type OR idx.type = CONCAT(items.type, '_meta'))
407
408	UNION
409
410	SELECT
411	items.id,
412	CASE
413	WHEN idx.type = CONCAT(items.type, '_meta') THEN CONCAT(items.type, '_meta')
414	ELSE items.type
415	END AS type
416	FROM
417	(
418	SELECT
419	p.ID AS id,
420	'post' AS type
421	FROM
422	$wpdb->posts p
423	WHERE p.post_status = 'publish'
424	UNION
425	SELECT
426	t.term_id AS id,
427	'term' AS type
428	FROM
429	$wpdb->terms t
430	) items
431	RIGHT JOIN (
432	SELECT
433	link_to AS id,
434	type_to AS type
435	FROM
436	$link_index_table_name
437	GROUP BY
438	link_to, type_to
439	) AS idx
440	ON items.id = idx.id
441	AND (idx.type = items.type OR idx.type = CONCAT(items.type, '_meta') OR idx.type = 'custom')
442	) AS combined_results;
443
444	";
445	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query is necessary for real-time data fetch and caching is not applicable for this use case.
446	return intval($wpdb->get_var($count_query));
447	}
448	}
449