query.php — Post Views Counter 1.3.8

post-views-counter / includes / query.php

post-views-counter / includes Last commit date

query.php

375 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	public function __construct() {
14	// actions
15	add_action( 'pre_get_posts', array( $this, 'extend_pre_query' ), 1 );
16
17	// filters
18	add_filter( 'query_vars', array( $this, 'query_vars' ) );
19	add_filter( 'posts_join', array( $this, 'posts_join' ), 10, 2 );
20	add_filter( 'posts_groupby', array( $this, 'posts_groupby' ), 10, 2 );
21	add_filter( 'posts_orderby', array( $this, 'posts_orderby' ), 10, 2 );
22	add_filter( 'posts_fields', array( $this, 'posts_fields' ), 10, 2 );
23	add_filter( 'the_posts', array( $this, 'the_posts' ), 10, 2 );
24	}
25
26	/**
27	* Register views_query var.
28	*
29	* @param array $query_vars
30	* @return array
31	*/
32	public function query_vars( $query_vars ) {
33	$query_vars[] = 'views_query';
34
35	return $query_vars;
36	}
37
38	/**
39	* Extend query with post_views orderby parameter.
40	*
41	* @param object $query
42	*/
43	public function extend_pre_query( $query ) {
44	if ( isset( $query->query_vars['orderby'] ) && $query->query_vars['orderby'] === 'post_views' )
45	$query->pvc_orderby = true;
46	}
47
48	/**
49	* Modify the db query to use post_views parameter.
50	*
51	* @global object $wpdb
52	* @param string $join
53	* @param object $query
54	* @return string
55	*/
56	public function posts_join( $join, $query ) {
57	$sql = '';
58	$query_chunks = array();
59
60	// views query?
61	if ( ! empty( $query->query['views_query'] ) ) {
62	if ( isset( $query->query['views_query']['inclusive'] ) )
63	$query->query['views_query']['inclusive'] = (bool) $query->query['views_query']['inclusive'];
64	else
65	$query->query['views_query']['inclusive'] = true;
66
67	// check after and before dates
68	foreach ( array( 'after' => '>', 'before' => '<' ) as $date => $type ) {
69	$year_ = null;
70	$month_ = null;
71	$week_ = null;
72	$day_ = null;
73
74	// check views query date
75	if ( ! empty( $query->query['views_query'][$date] ) ) {
76	// is it a date array?
77	if ( is_array( $query->query['views_query'][$date] ) ) {
78	// check views query $date date year
79	if ( ! empty( $query->query['views_query'][$date]['year'] ) )
80	$year_ = str_pad( (int) $query->query['views_query'][$date]['year'], 4, 0, STR_PAD_LEFT );
81
82	// check views query date month
83	if ( ! empty( $query->query['views_query'][$date]['month'] ) )
84	$month_ = str_pad( (int) $query->query['views_query'][$date]['month'], 2, 0, STR_PAD_LEFT );
85
86	// check views query date week
87	if ( ! empty( $query->query['views_query'][$date]['week'] ) )
88	$week_ = str_pad( (int) $query->query['views_query'][$date]['week'], 2, 0, STR_PAD_LEFT );
89
90	// check views query date day
91	if ( ! empty( $query->query['views_query'][$date]['day'] ) )
92	$day_ = str_pad( (int) $query->query['views_query'][$date]['day'], 2, 0, STR_PAD_LEFT );
93	// is it a date string?
94	} elseif ( is_string( $query->query['views_query'][$date] ) ) {
95	$time_ = strtotime( $query->query['views_query'][$date] );
96
97	// valid datetime?
98	if ( $time_ !== false ) {
99	// week does not exists here, string dates are always treated as year + month + day
100	list( $day_, $month_, $year_ ) = explode( ' ', date( "d m Y", $time_ ) );
101	}
102	}
103
104	// valid date?
105	if ( ! ( $year_ === null && $month_ === null && $week_ === null && $day_ === null ) ) {
106	$query_chunks[] = array(
107	'year' => $year_,
108	'month' => $month_,
109	'day' => $day_,
110	'week' => $week_,
111	'type' => $type . ( $query->query['views_query']['inclusive'] ? '=' : '' )
112	);
113	}
114	}
115	}
116
117	// any after, before query chunks?
118	if ( ! empty( $query_chunks ) ) {
119	$valid_dates = true;
120
121	// check only if both dates are in query
122	if ( count( $query_chunks ) === 2 ) {
123	// before and after dates should be the same
124	foreach ( array( 'year', 'month', 'day', 'week' ) as $date_type ) {
125	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 ) ) )
126	$valid_dates = false;
127	}
128	}
129
130	// after and before dates should be
131	if ( $valid_dates ) {
132	foreach ( $query_chunks as $chunk ) {
133	// year
134	if ( isset( $chunk['year'] ) ) {
135	// year, week
136	if ( isset( $chunk['week'] ) )
137	$sql .= " AND pvc.type = 1 AND pvc.period " . $chunk['type'] . " '" . $chunk['year'] . $chunk['week'] . "'";
138	// year, month
139	elseif ( isset( $chunk['month'] ) ) {
140	// year, month, day
141	if ( isset( $chunk['day'] ) )
142	$sql .= " AND pvc.type = 0 AND pvc.period " . $chunk['type'] . " '" . $chunk['year'] . $chunk['month'] . $chunk['day'] . "'";
143	// year, month
144	else
145	$sql .= " AND pvc.type = 2 AND pvc.period " . $chunk['type'] . " '" . $chunk['year'] . $chunk['month'] . "'";
146	// year
147	} else
148	$sql .= " AND pvc.type = 3 AND pvc.period " . $chunk['type'] . " '" . $chunk['year'] . "'";
149	// month
150	} elseif ( isset( $chunk['month'] ) ) {
151	// month, day
152	if ( isset( $chunk['day'] ) )
153	$sql .= " AND pvc.type = 0 AND RIGHT( pvc.period, 4 ) " . $chunk['type'] . " '" . $chunk['month'] . $chunk['day'] . "'";
154	// month
155	else
156	$sql .= " AND pvc.type = 2 AND RIGHT( pvc.period, 2 ) " . $chunk['type'] . " '" . $chunk['month'] . "'";
157	// week
158	} elseif ( isset( $chunk['week'] ) )
159	$sql .= " AND pvc.type = 1 AND RIGHT( pvc.period, 2 ) " . $chunk['type'] . " '" . $chunk['week'] . "'";
160	// day
161	elseif ( isset( $chunk['day'] ) )
162	$sql .= " AND pvc.type = 0 AND RIGHT( pvc.period, 2 ) " . $chunk['type'] . " '" . $chunk['day'] . "'";
163	}
164	}
165	}
166
167	// standard query
168	if ( $sql === '' ) {
169	// check year
170	if ( isset( $query->query['views_query']['year'] ) )
171	$year = (int) $query->query['views_query']['year'];
172
173	// check month
174	if ( isset( $query->query['views_query']['month'] ) )
175	$month = (int) $query->query['views_query']['month'];
176
177	// check week
178	if ( isset( $query->query['views_query']['week'] ) )
179	$week = (int) $query->query['views_query']['week'];
180
181	// check day
182	if ( isset( $query->query['views_query']['day'] ) )
183	$day = (int) $query->query['views_query']['day'];
184
185	// year
186	if ( isset( $year ) ) {
187	// year, week
188	if ( isset( $week ) && $this->is_valid_date( 'yw', $year, 0, 0, $week ) )
189	$sql = " AND pvc.type = 1 AND pvc.period = '" . str_pad( $year, 4, 0, STR_PAD_LEFT ) . str_pad( $week, 2, 0, STR_PAD_LEFT ) . "'";
190	// year, month
191	elseif ( isset( $month ) ) {
192	// year, month, day
193	if ( isset( $day ) && $this->is_valid_date( 'ymd', $year, $month, $day ) )
194	$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 ) . "'";
195	// year, month
196	elseif ( $this->is_valid_date( 'ym', $year, $month ) )
197	$sql = " AND pvc.type = 2 AND pvc.period = '" . str_pad( $year, 4, 0, STR_PAD_LEFT ) . str_pad( $month, 2, 0, STR_PAD_LEFT ) . "'";
198	// year
199	} elseif ( $this->is_valid_date( 'y', $year ) )
200	$sql = " AND pvc.type = 3 AND pvc.period = '" . str_pad( $year, 4, 0, STR_PAD_LEFT ) . "'";
201	// month
202	} elseif ( isset( $month ) ) {
203	// month, day
204	if ( isset( $day ) && $this->is_valid_date( 'md', 0, $month, $day ) ) {
205	$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 ) . "'";
206	// month
207	} elseif ( $this->is_valid_date( 'm', 0, $month ) )
208	$sql = " AND pvc.type = 2 AND RIGHT( pvc.period, 2 ) = '" . str_pad( $month, 2, 0, STR_PAD_LEFT ) . "'";
209	// week
210	} elseif ( isset( $week ) && $this->is_valid_date( 'w', 0, 0, 0, $week ) )
211	$sql = " AND pvc.type = 1 AND RIGHT( pvc.period, 2 ) = '" . str_pad( $week, 2, 0, STR_PAD_LEFT ) . "'";
212	// day
213	elseif ( isset( $day ) && $this->is_valid_date( 'd', 0, 0, $day ) )
214	$sql = " AND pvc.type = 0 AND RIGHT( pvc.period, 2 ) = '" . str_pad( $day, 2, 0, STR_PAD_LEFT ) . "'";
215	}
216
217	if ( $sql !== '' )
218	$query->pvc_query = true;
219	}
220
221	// is it sorted by post views?
222	if ( ( $sql === '' && isset( $query->pvc_orderby ) && $query->pvc_orderby ) \|\| apply_filters( 'pvc_extend_post_object', false, $query ) === true )
223	$sql = ' AND pvc.type = 4';
224
225	// add date range
226	if ( $sql !== '' ) {
227	global $wpdb;
228
229	$join .= " LEFT JOIN " . $wpdb->prefix . "post_views pvc ON pvc.id = " . $wpdb->prefix . "posts.ID" . $sql;
230	}
231
232	return $join;
233	}
234
235	/**
236	* Group posts using the post ID.
237	*
238	* @global object $wpdb
239	* @param string $groupby
240	* @param object $query
241	* @return string
242	*/
243	public function posts_groupby( $groupby, $query ) {
244	// is it sorted by post views or views_query is used?
245	if ( ( isset( $query->pvc_orderby ) && $query->pvc_orderby ) \|\| ( isset( $query->pvc_query ) && $query->pvc_query ) \|\| apply_filters( 'pvc_extend_post_object', false, $query ) === true ) {
246	global $pagenow;
247
248	// needed only for sorting
249	if ( $pagenow === 'upload.php' \|\| $pagenow === 'edit.php' )
250	$query->query['views_query']['hide_empty'] = false;
251
252	global $wpdb;
253
254	$groupby = trim( $groupby );
255
256	if ( strpos( $groupby, $wpdb->prefix . 'posts.ID' ) === false )
257	$groupby = ( $groupby !== '' ? $groupby . ', ' : '') . $wpdb->prefix . 'posts.ID';
258
259	if ( ! isset( $query->query['views_query']['hide_empty'] ) \|\| $query->query['views_query']['hide_empty'] === true )
260	$groupby .= ' HAVING post_views > 0';
261	}
262
263	return $groupby;
264	}
265
266	/**
267	* Order posts by post views.
268	*
269	* @global object $wpdb
270	* @param string $orderby
271	* @param object $query
272	* @return string
273	*/
274	public function posts_orderby( $orderby, $query ) {
275	// is it sorted by post views?
276	if ( ( isset( $query->pvc_orderby ) && $query->pvc_orderby ) ) {
277	global $wpdb;
278
279	$order = $query->get( 'order' );
280	$orderby = ( ! isset( $query->query['views_query']['hide_empty'] ) \|\| $query->query['views_query']['hide_empty'] === true ? 'post_views' : 'pvc.count' ) . ' ' . $order . ', ' . $wpdb->prefix . 'posts.ID ' . $order;
281	}
282
283	return $orderby;
284	}
285
286	/**
287	* Return post views in queried post objects.
288	*
289	* @param string $fields
290	* @param object $query
291	* @return string
292	*/
293	public function posts_fields( $fields, $query ) {
294	if ( ( ! isset( $query->query['fields'] ) \|\| $query->query['fields'] === '' ) && ( ( isset( $query->pvc_orderby ) && $query->pvc_orderby ) \|\| ( isset( $query->pvc_query ) && $query->pvc_query ) \|\| apply_filters( 'pvc_extend_post_object', false, $query ) === true ) )
295	$fields = $fields . ', SUM( IFNULL( pvc.count, 0 ) ) AS post_views';
296
297	return $fields;
298	}
299
300	/**
301	* Extend query object with total post views.
302	*
303	* @param array $posts
304	* @param object $query
305	* @return array
306	*/
307	public function the_posts( $posts, $query ) {
308	if ( ( isset( $query->pvc_orderby ) && $query->pvc_orderby ) \|\| ( isset( $query->pvc_query ) && $query->pvc_query ) \|\| apply_filters( 'pvc_extend_post_object', false, $query ) === true ) {
309	$sum = 0;
310
311	// any posts found?
312	if ( ! empty( $posts ) ) {
313	foreach ( $posts as $post ) {
314	if ( ! empty( $post->post_views ) )
315	$sum += (int) $post->post_views;
316	}
317	}
318
319	// pass total views
320	$query->total_views = $sum;
321	}
322
323	return $posts;
324	}
325
326	/**
327	* Validate date helper function.
328	*
329	* @param string $type
330	* @param int $year
331	* @param int $month
332	* @param int $day
333	* @param int $week
334	* @return bool
335	*/
336	private function is_valid_date( $type, $year = 0, $month = 0, $day = 0, $week = 0 ) {
337	switch ( $type ) {
338	case 'y':
339	$bool = ( $year >= 1 && $year <= 32767 );
340	break;
341
342	case 'yw':
343	$bool = ( $year >= 1 && $year <= 32767 && $week >= 0 && $week <= 53 );
344	break;
345
346	case 'ym':
347	$bool = ( $year >= 1 && $year <= 32767 && $month >= 1 && $month <= 12 );
348	break;
349
350	case 'ymd':
351	$bool = checkdate( $month, $day, $year );
352	break;
353
354	case 'm':
355	$bool = ( $month >= 1 && $month <= 12 );
356	break;
357
358	case 'md':
359	$bool = ( $month >= 1 && $month <= 12 && $day >= 1 && $day <= 31 );
360	break;
361
362	case 'w':
363	$bool = ( $week >= 0 && $week <= 53 );
364	break;
365
366	case 'd':
367	$bool = ( $day >= 1 && $day <= 31 );
368	break;
369	}
370
371	return $bool;
372	}
373
374	}
375