class-wordpress-popular-posts-query.php — WP Popular Posts 4.1.1

wordpress-popular-posts / includes / class-wordpress-popular-posts-query.php

wordpress-popular-posts / includes Last commit date

class-wordpress-popular-posts-query.php

467 lines

1	<?php
2	/**
3	* Queries the database for popular posts.
4	*
5	* To use this class, you must pass it an array of parameters (mostly the same ones used with
6	* the wpp_get_mostpopular() template tag). The very minimum required parameters are 'range', 'order_by'
7	* and 'limit'.
8	*
9	* eg.: $popular_posts = new WPP_Query( array('range' => 'last7days', 'order_by' => 'views', 'limit' => 5) );
10	*
11	* @since 4.0.0
12	* @package WordPressPopularPosts
13	* @subpackage WordPressPopularPosts/includes
14	*/
15
16	class WPP_Query {
17
18	/*
19	* Database query string.
20	*
21	* @since 4.0.0
22	* @access private
23	* @var string $query
24	*/
25	private $query;
26
27	/*
28	* List of posts.
29	*
30	* @since 4.0.0
31	* @access private
32	* @var array $posts
33	*/
34	private $posts = array();
35
36	/*
37	* Plugin options.
38	*
39	* @since 4.0.0
40	* @access private
41	* @var array $options
42	*/
43	private $options;
44
45	/*
46	* Constructor.
47	*
48	* @since 4.0.0
49	* @param array $options
50	*/
51	public function __construct( array $options = array() ){
52	$this->options = $options;
53	$this->build_query();
54	$this->run_query();
55	}
56
57	/*
58	* Builds the database query.
59	*
60	* @since 4.0.0
61	* @access private
62	*/
63	private function build_query(){
64
65	/*
66	* @var wpdb $wpdb
67	*/
68	global $wpdb;
69
70	if ( isset($wpdb) ) {
71
72	$this->options = WPP_Helper::merge_array_r(
73	WPP_Settings::$defaults[ 'widget_options' ],
74	(array) $this->options
75	);
76
77	$args = array();
78	$fields = "p.ID AS id, p.post_title AS title, p.post_author AS uid";
79	$table = "";
80	$join = "";
81	$where = "WHERE 1 = 1";
82	$groupby = "";
83	$orderby = "";
84	$limit = "LIMIT " . ( filter_var($this->options['limit'], FILTER_VALIDATE_INT) && $this->options['limit'] > 0 ? $this->options['limit'] : 10 ) . ( isset($this->options['offset']) && filter_var($this->options['offset'], FILTER_VALIDATE_INT) !== false && $this->options['offset'] >= 0 ? " OFFSET {$this->options['offset']}" : "" );
85
86	// Get post date
87	if ( isset($this->options['stats_tag']['date']['active']) && $this->options['stats_tag']['date']['active'] ) {
88	$fields .= ", p.post_date AS date";
89	}
90
91	// Get post excerpt $instance
92	if ( isset($this->options['post-excerpt']['active']) && $this->options['post-excerpt']['active'] ) {
93	$fields .= ", p.post_excerpt AS post_excerpt, p.post_content AS post_content";
94	}
95
96	// Get entries from these post types
97	$post_types = array( 'post', 'page' );
98
99	if ( isset($this->options['post_type']) && !empty($this->options['post_type']) ) {
100
101	$post_types = explode( ",", $this->options['post_type'] );
102	$pt = '';
103	$where .= " AND p.post_type IN(";
104
105	foreach( $post_types as $post_type ) {
106	$pt .= "%s, ";
107	array_push( $args, trim($post_type) );
108	}
109
110	$where .= rtrim($pt, ", ") . ")";
111
112	}
113	else {
114	$where .= " AND p.post_type IN('post', 'page')";
115	}
116
117	// Get entries from these authors
118	if ( isset($this->options['author']) && !empty($this->options['author']) ) {
119
120	$author_IDs = explode( ",", $this->options['author'] );
121	$uid = '';
122	$where .= " AND p.post_author IN(";
123
124	foreach( $author_IDs as $author_ID ) {
125	$uid .= "%d, ";
126	array_push( $args, trim($author_ID) );
127	}
128
129	$where .= rtrim($uid, ", ") . ")";
130
131	}
132
133	// Get / exclude entries from this taxonomy
134	if (
135	( isset($this->options['cat']) && !empty($this->options['cat']) )
136	\|\| ( isset($this->options['term_id']) && !empty($this->options['term_id']) )
137	) {
138
139	if ( isset($this->options['taxonomy']) && !empty($this->options['taxonomy']) ) {
140
141	$registered_taxonomies = get_taxonomies( array('public' => true) );
142
143	// Invalid taxonomy, fallback to "category"
144	if ( !isset($registered_taxonomies[$this->options['taxonomy']]) ) {
145	$this->options['taxonomy'] = 'category';
146	}
147
148	} // Default to "category"
149	else {
150	$this->options['taxonomy'] = 'category';
151	}
152
153	if ( isset($this->options['cat']) && !empty($this->options['cat']) ) {
154	$this->options['term_id'] = $this->options['cat'];
155	}
156
157	$term_IDs = explode( ",", $this->options['term_id'] );
158	$in_term_IDs = array();
159	$out_term_IDs = array();
160
161	foreach( $term_IDs as $term_ID ) {
162
163	if ( $term_ID >= 0 )
164	$in_term_IDs[] = trim( $term_ID );
165	else
166	$out_term_IDs[] = trim( $term_ID ) * -1;
167
168	}
169
170	if ( !empty($in_term_IDs) ) {
171
172	$where .= " AND p.ID IN (
173	SELECT object_id
174	FROM `{$wpdb->term_relationships}` AS r
175	JOIN `{$wpdb->term_taxonomy}` AS x ON x.term_taxonomy_id = r.term_taxonomy_id
176	WHERE x.taxonomy = '{$this->options['taxonomy']}'";
177
178	$inTID = '';
179
180	foreach( $in_term_IDs as $in_term_ID ) {
181	$inTID .= "%d, ";
182	array_push( $args, $in_term_ID );
183	}
184
185	$where .= " AND x.term_id IN(" . rtrim($inTID, ", ") . ") )";
186
187	}
188
189	if ( !empty($out_term_IDs) ) {
190
191	$post_ids = get_posts(
192	array(
193	'post_type' => $post_types,
194	'posts_per_page' => -1,
195	'tax_query' => array(
196	array(
197	'taxonomy' => $this->options['taxonomy'],
198	'field' => 'id',
199	'terms' => $out_term_IDs,
200	),
201	),
202	'fields' => 'ids'
203	)
204	);
205
206	if ( is_array($post_ids) && !empty($post_ids) ) {
207
208	if ( isset($this->options['pid']) && !empty($this->options['pid']) ) {
209	$this->options['pid'] .= "," . implode( ",", $post_ids );
210	}
211	else {
212	$this->options['pid'] = implode( ",", $post_ids );
213	}
214
215	}
216
217	}
218
219	}
220
221	// Exclude these entries from the listing
222	if ( isset($this->options['pid']) && !empty($this->options['pid']) ) {
223
224	$excluded_post_IDs = explode( ",", $this->options['pid'] );
225	$xpid = '';
226	$where .= " AND p.ID NOT IN(";
227
228	foreach( $excluded_post_IDs as $excluded_post_ID ) {
229	$xpid .= "%d, ";
230	array_push( $args, trim($excluded_post_ID) );
231	}
232
233	$where .= rtrim($xpid, ", ") . ")";
234
235	}
236
237	// All-time range
238	if ( "all" == $this->options['range'] ) {
239
240	// Order by views count
241	if ( "comments" != $this->options['order_by'] ) {
242
243	$table = "`{$wpdb->prefix}popularpostsdata` v";
244	$join = "LEFT JOIN `{$wpdb->posts}` p ON v.postid = p.ID";
245
246	// Order by views
247	if ( "views" == $this->options['order_by'] ) {
248
249	if ( !isset($this->options['stats_tag']['views']) \|\| $this->options['stats_tag']['views'] ) {
250	$fields .= ", v.pageviews";
251	}
252
253	$orderby = "ORDER BY v.pageviews DESC";
254
255	}
256	// Order by average views
257	else {
258
259	$now = current_time( 'mysql' );
260
261	$fields .= ", ( v.pageviews/(IF ( DATEDIFF('{$now}', MIN(v.day)) > 0, DATEDIFF('{$now}', MIN(v.day)), 1) ) ) AS avg_views";
262	$groupby = "GROUP BY v.postid";
263	$orderby = "ORDER BY avg_views DESC";
264
265	}
266
267	// Display comments count, too
268	if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
269	$fields .= ", p.comment_count";
270	}
271
272	}
273	// Order by comments count
274	else {
275
276	$table = "`{$wpdb->posts}` p";
277	$where .= " AND p.comment_count > 0";
278	$orderby = "ORDER BY p.comment_count DESC";
279
280	// Display comment count
281	if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
282	$fields .= ", p.comment_count";
283	}
284
285	// Display views count, too
286	if ( isset($this->options['stats_tag']['views']) && $this->options['stats_tag']['views'] ) {
287	$fields .= ", IFNULL(v.pageviews, 0) AS pageviews";
288	$join = "LEFT JOIN `{$wpdb->prefix}popularpostsdata` v ON p.ID = v.postid";
289	}
290
291	}
292
293	}
294	// Custom time range
295	else {
296
297	$now = current_time( 'mysql' );
298
299	// Determine time range
300	switch( $this->options['range'] ){
301	case "last24hours":
302	case "daily":
303	$interval = "24 HOUR";
304	break;
305
306	case "last7days":
307	case "weekly":
308	$interval = "6 DAY";
309	break;
310
311	case "last30days":
312	case "monthly":
313	$interval = "29 DAY";
314	break;
315
316	case "custom":
317	$time_units = array( "MINUTE", "HOUR", "DAY", "WEEK", "MONTH" );
318	$interval = "24 HOUR";
319
320	// Valid time unit
321	if (
322	isset( $this->options['time_unit'] )
323	&& in_array( strtoupper( $this->options['time_unit'] ), $time_units )
324	&& isset( $this->options['time_quantity'] )
325	&& filter_var( $this->options['time_quantity'], FILTER_VALIDATE_INT )
326	&& $this->options['time_quantity'] > 0
327	) {
328	$interval = "{$this->options['time_quantity']} " . strtoupper( $this->options['time_unit'] );
329	}
330
331	break;
332
333	default:
334	$interval = "24 HOUR";
335	break;
336	}
337
338	// Get entries published within the specified time range
339	if ( isset($this->options['freshness']) && $this->options['freshness'] ) {
340	$where .= " AND p.post_date > DATE_SUB('{$now}', INTERVAL {$interval})";
341	}
342
343	// Order by views count
344	if ( "comments" != $this->options['order_by'] ) {
345
346	$table = "`{$wpdb->prefix}popularpostssummary` v";
347	$join = "LEFT JOIN `{$wpdb->posts}` p ON v.postid = p.ID";
348	$where .= " AND v.view_datetime > DATE_SUB('{$now}', INTERVAL {$interval})";
349	$groupby = "GROUP BY v.postid";
350
351	// Order by views
352	if ( "views" == $this->options['order_by'] ) {
353
354	if ( !isset($this->options['stats_tag']['views']) \|\| $this->options['stats_tag']['views'] ) {
355	$fields .= ", SUM(v.pageviews) AS pageviews";
356	$orderby = "ORDER BY pageviews DESC";
357	}
358	else {
359	$orderby = "ORDER BY SUM(v.pageviews) DESC";
360	}
361
362	}
363	// Order by average views
364	else {
365	$fields .= ", ( SUM(v.pageviews)/(IF ( DATEDIFF('{$now}', DATE_SUB('{$now}', INTERVAL {$interval})) > 0, DATEDIFF('{$now}', DATE_SUB('{$now}', INTERVAL {$interval})), 1) ) ) AS avg_views";
366	$orderby = "ORDER BY avg_views DESC";
367	}
368
369	// Display comments count, too
370	if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
371	$fields .= ", IFNULL(c.comment_count, 0) AS comment_count";
372	$join .= " LEFT JOIN (SELECT comment_post_ID, COUNT(comment_post_ID) AS comment_count FROM `{$wpdb->comments}` WHERE comment_date_gmt > DATE_SUB('{$now}', INTERVAL {$interval}) AND comment_approved = 1 GROUP BY comment_post_ID) c ON p.ID = c.comment_post_ID";
373	}
374
375	}
376	// Order by comments count
377	else {
378
379	$table = "`{$wpdb->comments}` c";
380	$join = "LEFT JOIN {$wpdb->posts} p ON c.comment_post_ID = p.ID";
381	$where .= " AND c.comment_date_gmt > DATE_SUB('{$now}', INTERVAL {$interval}) AND c.comment_approved = 1";
382	$groupby = "GROUP BY c.comment_post_ID";
383
384	// Display comment count
385	if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
386	$fields .= ", COUNT(c.comment_post_ID) AS comment_count";
387	$orderby = "ORDER BY comment_count DESC";
388	}
389	else {
390	$orderby = "ORDER BY COUNT(c.comment_post_ID) DESC";
391	}
392
393	// Display views count, too
394	if ( isset($this->options['stats_tag']['views']) && $this->options['stats_tag']['views'] ) {
395	$fields .= ", IFNULL(v.pageviews, 0) AS pageviews";
396	$join .= " LEFT JOIN (SELECT postid, SUM(pageviews) AS pageviews FROM `{$wpdb->prefix}popularpostssummary` WHERE view_datetime > DATE_SUB('{$now}', INTERVAL {$interval}) GROUP BY postid) v ON p.ID = v.postid";
397	}
398
399	}
400
401	}
402
403	// List only published, non password-protected posts
404	$where .= " AND p.post_password = '' AND p.post_status = 'publish'";
405
406	if ( !empty($args) ) {
407	$where = $wpdb->prepare( $where, $args );
408	}
409
410	$fields = apply_filters( 'wpp_query_fields', $fields, $this->options );
411	$table = apply_filters( 'wpp_query_table', $table, $this->options );
412	$join = apply_filters( 'wpp_query_join', $join, $this->options );
413	$where = apply_filters( 'wpp_query_where', $where, $this->options );
414	$groupby = apply_filters( 'wpp_query_group_by', $groupby, $this->options );
415	$orderby = apply_filters( 'wpp_query_order_by', $orderby, $this->options );
416	$limit = apply_filters( 'wpp_query_limit', $limit, $this->options );
417
418	// Finally, build the query
419	$query = "SELECT {$fields} FROM {$table} {$join} {$where} {$groupby} {$orderby} {$limit};";
420	//$this->query = ( !empty($args) && !has_filter('wpp_query_where') ) ? $wpdb->prepare( $query, $args ) : $query;
421	$this->query = $query;
422
423	}
424
425	}
426
427	/*
428	* Queries the database.
429	*
430	* @since 4.0.0
431	* @access private
432	*/
433	private function run_query(){
434
435	/*
436	* @var wpdb $wpdb
437	*/
438	global $wpdb;
439
440	if ( isset($wpdb) && !empty($this->query) && !is_wp_error($this->query) ) {
441	$this->posts = $wpdb->get_results( $this->query );
442	}
443
444	}
445
446	/*
447	* Returns the query string.
448	*
449	* @since 4.0.0
450	* @return WP_Error\|string Query string on success, WP_Error on failure
451	*/
452	public function get_query(){
453	return $this->query;
454	}
455
456	/*
457	* Returns the list of posts.
458	*
459	* @since 4.0.0
460	* @return array
461	*/
462	public function get_posts(){
463	return $this->posts;
464	}
465
466	} // end WPP_Query class
467