class-query.php — Post Views Counter 1.3.12

post-views-counter / includes / class-query.php

post-views-counter / includes Last commit date

class-admin.php 3 years ago class-columns.php 3 years ago class-counter.php 3 years ago class-crawler-detect.php 3 years ago class-cron.php 3 years ago class-dashboard.php 3 years ago class-frontend.php 3 years ago class-functions.php 3 years ago class-query.php 3 years ago class-settings-api.php 3 years ago class-settings.php 3 years ago class-update.php 3 years ago class-widgets.php 3 years ago functions.php 3 years ago

class-query.php

521 lines

1	<?php
2	// exit if accessed directly
3	if ( ! defined( 'ABSPATH' ) )
4	exit;
5
6	/**
7	* Post_Views_Counter_Query class.
8	*
9	* @class Post_Views_Counter_Query
10	*/
11	class Post_Views_Counter_Query {
12
13	private $join_sql = '';
14
15	/**
16	* Class constructor.
17	*
18	* @return void
19	*/
20	public function __construct() {
21	// actions
22	add_action( 'pre_get_posts', [ $this, 'extend_pre_query' ], 1 );
23
24	// filters
25	add_filter( 'query_vars', [ $this, 'query_vars' ] );
26	add_filter( 'posts_join', [ $this, 'posts_join' ], 100, 2 );
27	add_filter( 'posts_groupby', [ $this, 'posts_groupby' ], 10, 2 );
28	add_filter( 'posts_orderby', [ $this, 'posts_orderby' ], 10, 2 );
29	add_filter( 'posts_distinct', [ $this, 'posts_distinct' ], 10, 2 );
30	add_filter( 'posts_fields', [ $this, 'posts_fields' ], 10, 2 );
31	add_filter( 'the_posts', [ $this, 'the_posts' ], 10, 2 );
32	}
33
34	/**
35	* Register views_query var.
36	*
37	* @param array $query_vars
38	* @return array
39	*/
40	public function query_vars( $query_vars ) {
41	$query_vars[] = 'views_query';
42
43	return $query_vars;
44	}
45
46	/**
47	* Extend query with post_views orderby parameter.
48	*
49	* @param object $query
50	* @return void
51	*/
52	public function extend_pre_query( $query ) {
53	if ( isset( $query->query_vars['orderby'] ) && $query->query_vars['orderby'] === 'post_views' )
54	$query->pvc_orderby = true;
55	}
56
57	/**
58	* Modify the database query to use post_views parameter.
59	*
60	* @global object $wpdb
61	*
62	* @param string $join
63	* @param object $query
64	* @return string
65	*/
66	public function posts_join( $join, $query ) {
67	$sql = '';
68	$query_chunks = [];
69
70	// views query?
71	if ( ! empty( $query->query['views_query'] ) ) {
72	if ( isset( $query->query['views_query']['inclusive'] ) )
73	$query->query['views_query']['inclusive'] = (bool) $query->query['views_query']['inclusive'];
74	else
75	$query->query['views_query']['inclusive'] = true;
76
77	// check after and before dates
78	foreach ( [ 'after' => '>', 'before' => '<' ] as $date => $type ) {
79	$year_ = null;
80	$month_ = null;
81	$week_ = null;
82	$day_ = null;
83
84	// check views query date
85	if ( ! empty( $query->query['views_query'][$date] ) ) {
86	// is it a date array?
87	if ( is_array( $query->query['views_query'][$date] ) ) {
88	// check views query $date date year
89	if ( ! empty( $query->query['views_query'][$date]['year'] ) )
90	$year_ = str_pad( (int) $query->query['views_query'][$date]['year'], 4, 0, STR_PAD_LEFT );
91
92	// check views query date month
93	if ( ! empty( $query->query['views_query'][$date]['month'] ) )
94	$month_ = str_pad( (int) $query->query['views_query'][$date]['month'], 2, 0, STR_PAD_LEFT );
95
96	// check views query date week
97	if ( ! empty( $query->query['views_query'][$date]['week'] ) )
98	$week_ = str_pad( (int) $query->query['views_query'][$date]['week'], 2, 0, STR_PAD_LEFT );
99
100	// check views query date day
101	if ( ! empty( $query->query['views_query'][$date]['day'] ) )
102	$day_ = str_pad( (int) $query->query['views_query'][$date]['day'], 2, 0, STR_PAD_LEFT );
103	// is it a date string?
104	} elseif ( is_string( $query->query['views_query'][$date] ) ) {
105	$time_ = strtotime( $query->query['views_query'][$date] );
106
107	// valid datetime?
108	if ( $time_ !== false ) {
109	// week does not exists here, string dates are always treated as year + month + day
110	list( $day_, $month_, $year_ ) = explode( ' ', date( "d m Y", $time_ ) );
111	}
112	}
113
114	// valid date?
115	if ( ! ( $year_ === null && $month_ === null && $week_ === null && $day_ === null ) ) {
116	$query_chunks[] = [
117	'year' => $year_,
118	'month' => $month_,
119	'day' => $day_,
120	'week' => $week_,
121	'type' => $type . ( $query->query['views_query']['inclusive'] ? '=' : '' )
122	];
123	}
124	}
125	}
126
127	// any after, before query chunks?
128	if ( ! empty( $query_chunks ) ) {
129	$valid_dates = true;
130
131	// check only if both dates are in query
132	if ( count( $query_chunks ) === 2 ) {
133	// before and after dates should be the same
134	foreach ( [ 'year', 'month', 'day', 'week' ] as $date_type ) {
135	if ( ! ( ( $query_chunks[0][$date_type] !== null && $query_chunks[1][$date_type] !== null ) \|\| ( $query_chunks[0][$date_type] === null && $query_chunks[1][$date_type] === null ) ) )
136	$valid_dates = false;
137	}
138	}
139
140	// after and before dates should be both valid
141	if ( $valid_dates ) {
142	foreach ( $query_chunks as $chunk ) {
143	// year
144	if ( isset( $chunk['year'] ) ) {
145	// year, week
146	if ( isset( $chunk['week'] ) )
147	$sql .= " AND pvc.type = 1 AND pvc.period " . $chunk['type'] . " '" . $chunk['year'] . $chunk['week'] . "'";
148	// year, month
149	elseif ( isset( $chunk['month'] ) ) {
150	// year, month, day
151	if ( isset( $chunk['day'] ) )
152	$sql .= " AND pvc.type = 0 AND pvc.period " . $chunk['type'] . " '" . $chunk['year'] . $chunk['month'] . $chunk['day'] . "'";
153	// year, month
154	else
155	$sql .= " AND pvc.type = 2 AND pvc.period " . $chunk['type'] . " '" . $chunk['year'] . $chunk['month'] . "'";
156	// year
157	} else
158	$sql .= " AND pvc.type = 3 AND pvc.period " . $chunk['type'] . " '" . $chunk['year'] . "'";
159	// month
160	} elseif ( isset( $chunk['month'] ) ) {
161	// month, day
162	if ( isset( $chunk['day'] ) )
163	$sql .= " AND pvc.type = 0 AND RIGHT( pvc.period, 4 ) " . $chunk['type'] . " '" . $chunk['month'] . $chunk['day'] . "'";
164	// month
165	else
166	$sql .= " AND pvc.type = 2 AND RIGHT( pvc.period, 2 ) " . $chunk['type'] . " '" . $chunk['month'] . "'";
167	// week
168	} elseif ( isset( $chunk['week'] ) )
169	$sql .= " AND pvc.type = 1 AND RIGHT( pvc.period, 2 ) " . $chunk['type'] . " '" . $chunk['week'] . "'";
170	// day
171	elseif ( isset( $chunk['day'] ) )
172	$sql .= " AND pvc.type = 0 AND RIGHT( pvc.period, 2 ) " . $chunk['type'] . " '" . $chunk['day'] . "'";
173	}
174	}
175	}
176
177	// standard query
178	if ( $sql === '' ) {
179	// check year
180	if ( isset( $query->query['views_query']['year'] ) )
181	$year = (int) $query->query['views_query']['year'];
182
183	// check month
184	if ( isset( $query->query['views_query']['month'] ) )
185	$month = (int) $query->query['views_query']['month'];
186
187	// check week
188	if ( isset( $query->query['views_query']['week'] ) )
189	$week = (int) $query->query['views_query']['week'];
190
191	// check day
192	if ( isset( $query->query['views_query']['day'] ) )
193	$day = (int) $query->query['views_query']['day'];
194
195	// year
196	if ( isset( $year ) ) {
197	// year, week
198	if ( isset( $week ) && $this->is_date_valid( 'yw', $year, 0, 0, $week ) )
199	$sql = " AND pvc.type = 1 AND pvc.period = '" . str_pad( $year, 4, 0, STR_PAD_LEFT ) . str_pad( $week, 2, 0, STR_PAD_LEFT ) . "'";
200	// year, month
201	elseif ( isset( $month ) ) {
202	// year, month, day
203	if ( isset( $day ) && $this->is_date_valid( 'ymd', $year, $month, $day ) )
204	$sql = " AND pvc.type = 0 AND pvc.period = '" . str_pad( $year, 4, 0, STR_PAD_LEFT ) . str_pad( $month, 2, 0, STR_PAD_LEFT ) . str_pad( $day, 2, 0, STR_PAD_LEFT ) . "'";
205	// year, month
206	elseif ( $this->is_date_valid( 'ym', $year, $month ) )
207	$sql = " AND pvc.type = 2 AND pvc.period = '" . str_pad( $year, 4, 0, STR_PAD_LEFT ) . str_pad( $month, 2, 0, STR_PAD_LEFT ) . "'";
208	// year
209	} elseif ( $this->is_date_valid( 'y', $year ) )
210	$sql = " AND pvc.type = 3 AND pvc.period = '" . str_pad( $year, 4, 0, STR_PAD_LEFT ) . "'";
211	// month
212	} elseif ( isset( $month ) ) {
213	// month, day
214	if ( isset( $day ) && $this->is_date_valid( 'md', 0, $month, $day ) ) {
215	$sql = " AND pvc.type = 0 AND RIGHT( pvc.period, 4 ) = '" . str_pad( $month, 2, 0, STR_PAD_LEFT ) . str_pad( $day, 2, 0, STR_PAD_LEFT ) . "'";
216	// month
217	} elseif ( $this->is_date_valid( 'm', 0, $month ) )
218	$sql = " AND pvc.type = 2 AND RIGHT( pvc.period, 2 ) = '" . str_pad( $month, 2, 0, STR_PAD_LEFT ) . "'";
219	// week
220	} elseif ( isset( $week ) && $this->is_date_valid( 'w', 0, 0, 0, $week ) )
221	$sql = " AND pvc.type = 1 AND RIGHT( pvc.period, 2 ) = '" . str_pad( $week, 2, 0, STR_PAD_LEFT ) . "'";
222	// day
223	elseif ( isset( $day ) && $this->is_date_valid( 'd', 0, 0, $day ) )
224	$sql = " AND pvc.type = 0 AND RIGHT( pvc.period, 2 ) = '" . str_pad( $day, 2, 0, STR_PAD_LEFT ) . "'";
225	}
226
227	if ( $sql !== '' )
228	$query->pvc_query = true;
229	}
230
231	// is it sorted by post views?
232	if ( ( $sql === '' && isset( $query->pvc_orderby ) && $query->pvc_orderby ) \|\| apply_filters( 'pvc_extend_post_object', false, $query ) === true )
233	$sql = ' AND pvc.type = 4';
234
235	// add date range
236	if ( $sql !== '' ) {
237	global $wpdb;
238
239	$join .= " LEFT JOIN " . $wpdb->prefix . "post_views pvc ON pvc.id = " . $wpdb->prefix . "posts.ID" . $sql;
240
241	$this->join_sql = $join;
242	}
243
244	return $join;
245	}
246
247	/**
248	* Group posts using the post ID.
249	*
250	* @global object $wpdb
251	* @global string $pagenow
252	*
253	* @param string $groupby
254	* @param object $query
255	* @return string
256	*/
257	public function posts_groupby( $groupby, $query ) {
258	// is it sorted by post views or views_query is used?
259	if ( ( isset( $query->pvc_orderby ) && $query->pvc_orderby ) \|\| ( isset( $query->pvc_query ) && $query->pvc_query ) \|\| apply_filters( 'pvc_extend_post_object', false, $query ) === true ) {
260	global $pagenow;
261
262	// needed only for sorting
263	if ( $pagenow === 'upload.php' \|\| $pagenow === 'edit.php' )
264	$query->query['views_query']['hide_empty'] = false;
265
266	global $wpdb;
267
268	$groupby = trim( $groupby );
269	$groupby_aliases = [];
270	$groupby_values = [];
271	$groupby_sql = '';
272	$groupby_set = false;
273
274	// standard group by
275	if ( strpos( $groupby, $wpdb->prefix . 'posts.ID' ) === false )
276	$groupby_aliases[] = $wpdb->prefix . 'posts.ID';
277	else
278	$groupby_set = true;
279
280	// tax query group by
281	$groupby_aliases[] = $this->get_groupby_meta_aliases( $query );
282
283	// meta query group by
284	if ( $this->join_sql ) {
285	$groupby_aliases[] = $this->get_groupby_tax_aliases( $query, $this->join_sql );
286
287	// clear join to avoid possible issues
288	$this->join_sql = '';
289	}
290
291	// any group by aliases?
292	if ( ! empty( $groupby_aliases ) ) {
293	foreach ( $groupby_aliases as $alias ) {
294	if ( is_array( $alias ) ) {
295	$groupby_values = array_merge( $groupby_values, $alias );
296	} else
297	$groupby_values[] = $alias;
298	}
299	}
300
301	// any group by values?
302	if ( ! empty( $groupby_values ) ) {
303	$groupby = ( $groupby !== '' ? $groupby . ', ' : '' ) . implode( ', ', $groupby_values );
304
305	// set group by flag
306	$groupby_set = true;
307	}
308
309	if ( $groupby_set )
310	$query->pvc_groupby = true;
311
312	// hide empty?
313	if ( ! isset( $query->query['views_query']['hide_empty'] ) \|\| $query->query['views_query']['hide_empty'] === true )
314	$groupby .= ' HAVING post_views > 0';
315	}
316
317	return $groupby;
318	}
319
320	/**
321	* Order posts by post views.
322	*
323	* @global object $wpdb
324	*
325	* @param string $orderby
326	* @param object $query
327	* @return string
328	*/
329	public function posts_orderby( $orderby, $query ) {
330	// is it sorted by post views?
331	if ( ( isset( $query->pvc_orderby ) && $query->pvc_orderby ) ) {
332	global $wpdb;
333
334	$order = $query->get( 'order' );
335	$orderby = 'post_views ' . $order . ', ' . $wpdb->prefix . 'posts.ID ' . $order;
336	}
337
338	return $orderby;
339	}
340
341	/**
342	* Add DISTINCT clause.
343	*
344	* @param string $distinct
345	* @param object $query
346	* @return string
347	*/
348	public function posts_distinct( $distinct, $query ) {
349	if ( ( ( isset( $query->pvc_groupby ) && $query->pvc_groupby ) \|\| ( isset( $query->pvc_orderby ) && $query->pvc_orderby ) \|\| ( isset( $query->pvc_query ) && $query->pvc_query ) \|\| apply_filters( 'pvc_extend_post_object', false, $query ) === true ) && ( strpos( $distinct, 'DISTINCT' ) === false ) )
350	$distinct = $distinct . ' DISTINCT ';
351
352	return $distinct;
353	}
354
355	/**
356	* Return post views in queried post objects.
357	*
358	* @param string $fields
359	* @param object $query
360	* @return string
361	*/
362	public function posts_fields( $fields, $query ) {
363	if ( ( ! isset( $query->query['fields'] ) \|\| $query->query['fields'] === '' \|\| $query->query['fields'] === 'all' ) && ( ( isset( $query->pvc_orderby ) && $query->pvc_orderby ) \|\| ( isset( $query->pvc_query ) && $query->pvc_query ) \|\| apply_filters( 'pvc_extend_post_object', false, $query ) === true ) )
364	$fields = $fields . ', SUM( COALESCE( pvc.count, 0 ) ) AS post_views';
365
366	return $fields;
367	}
368
369	/**
370	* Get tax table aliases from query.
371	*
372	* @param object $query
373	* @param string $join_sql
374	* @return array
375	*/
376	private function get_groupby_tax_aliases( $query, $join_sql ) {
377	$groupby = [];
378
379	// trim join sql
380	$join_sql = trim( $join_sql );
381
382	// any join sql? valid query with tax query?
383	if ( $join_sql !== '' && is_a( $query, 'WP_Query' ) && ! empty( $query->tax_query ) && is_a( $query->tax_query, 'WP_Tax_Query' ) ) {
384	// unfortunately there is no way to get table_aliases by native function
385	// tax query does not have get_clauses either like meta query does
386	// we have to find aliases the hard way
387	$chunks = explode( 'JOIN', $join_sql );
388
389	// any join clauses?
390	if ( ! empty( $chunks ) ) {
391	$aliases = [];
392
393	foreach ( $chunks as $chunk ) {
394	// standard join
395	if ( strpos( $chunk, 'wp_term_relationships ON' ) !== false )
396	$aliases[] = 'wp_term_relationships';
397	// alias join
398	elseif ( strpos( $chunk, 'wp_term_relationships AS' ) !== false && preg_match( '/wp_term_relationships AS ([a-z0-9]+) ON/i', $chunk, $matches ) === 1 )
399	$aliases[] = $matches[1];
400	}
401
402	// any aliases?
403	if ( ! empty( $aliases ) ) {
404	foreach ( array_unique( $aliases ) as $alias ) {
405	$groupby[] = $alias . '.term_taxonomy_id';
406	}
407	}
408	}
409	}
410
411	return $groupby;
412	}
413
414	/**
415	* Get meta table aliases from query.
416	*
417	* @param object $query
418	* @return array
419	*/
420	private function get_groupby_meta_aliases( $query ) {
421	$groupby = [];
422
423	// valid query with meta query?
424	if ( is_a( $query, 'WP_Query' ) && ! empty( $query->meta_query ) && is_a( $query->meta_query, 'WP_Meta_Query' ) ) {
425	// get meta clauses, we can't use table_aliases here since it's protected value
426	$clauses = $query->meta_query->get_clauses();
427
428	// any meta clauses?
429	if ( ! empty( $clauses ) ) {
430	$aliases = [];
431
432	foreach ( $clauses as $clause ) {
433	$aliases[] = $clause['alias'];
434	}
435
436	// any aliases?
437	if ( ! empty( $aliases ) ) {
438	foreach ( array_unique( $aliases ) as $alias ) {
439	$groupby[] = $alias . '.meta_id';
440	}
441	}
442	}
443	}
444
445	return $groupby;
446	}
447
448	/**
449	* Extend query object with total post views.
450	*
451	* @param array $posts
452	* @param object $query
453	* @return array
454	*/
455	public function the_posts( $posts, $query ) {
456	if ( ( isset( $query->pvc_orderby ) && $query->pvc_orderby ) \|\| ( isset( $query->pvc_query ) && $query->pvc_query ) \|\| apply_filters( 'pvc_extend_post_object', false, $query ) === true ) {
457	$sum = 0;
458
459	// any posts found?
460	if ( ! empty( $posts ) ) {
461	foreach ( $posts as $post ) {
462	if ( ! empty( $post->post_views ) )
463	$sum += (int) $post->post_views;
464	}
465	}
466
467	// pass total views
468	$query->total_views = $sum;
469	}
470
471	return $posts;
472	}
473
474	/**
475	* Check whether date is valid.
476	*
477	* @param string $type
478	* @param int $year
479	* @param int $month
480	* @param int $day
481	* @param int $week
482	* @return bool
483	*/
484	private function is_date_valid( $type, $year = 0, $month = 0, $day = 0, $week = 0 ) {
485	switch ( $type ) {
486	case 'y':
487	$bool = ( $year >= 1 && $year <= 32767 );
488	break;
489
490	case 'yw':
491	$bool = ( $year >= 1 && $year <= 32767 && $week >= 0 && $week <= 53 );
492	break;
493
494	case 'ym':
495	$bool = ( $year >= 1 && $year <= 32767 && $month >= 1 && $month <= 12 );
496	break;
497
498	case 'ymd':
499	$bool = checkdate( $month, $day, $year );
500	break;
501
502	case 'm':
503	$bool = ( $month >= 1 && $month <= 12 );
504	break;
505
506	case 'md':
507	$bool = ( $month >= 1 && $month <= 12 && $day >= 1 && $day <= 31 );
508	break;
509
510	case 'w':
511	$bool = ( $week >= 0 && $week <= 53 );
512	break;
513
514	case 'd':
515	$bool = ( $day >= 1 && $day <= 31 );
516	break;
517	}
518
519	return $bool;
520	}
521	}