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

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

wordpress-popular-posts / includes Last commit date

class-wordpress-popular-posts-query.php

459 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	if ( isset($this->options['post_type']) && !empty($this->options['post_type']) ) {
98
99	$post_types = explode( ",", $this->options['post_type'] );
100	$pt = '';
101	$where .= " AND p.post_type IN(";
102
103	foreach( $post_types as $post_type ) {
104	$pt .= "%s, ";
105	array_push( $args, trim($post_type) );
106	}
107
108	$where .= rtrim($pt, ", ") . ")";
109
110	}
111	else {
112	$where .= " AND p.post_type IN('post', 'page')";
113	}
114
115	// Get entries from these authors
116	if ( isset($this->options['author']) && !empty($this->options['author']) ) {
117
118	$author_IDs = explode( ",", $this->options['author'] );
119	$uid = '';
120	$where .= " AND p.post_author IN(";
121
122	foreach( $author_IDs as $author_ID ) {
123	$uid .= "%d, ";
124	array_push( $args, trim($author_ID) );
125	}
126
127	$where .= rtrim($uid, ", ") . ")";
128
129	}
130
131	// Get / exclude entries from this taxonomy
132	if (
133	( isset($this->options['cat']) && !empty($this->options['cat']) )
134	\|\| ( isset($this->options['term_id']) && !empty($this->options['term_id']) )
135	) {
136
137	if ( isset($this->options['taxonomy']) && !empty($this->options['taxonomy']) ) {
138
139	$registered_taxonomies = get_taxonomies( array('public' => true) );
140
141	// Invalid taxonomy, fallback to "category"
142	if ( !isset($registered_taxonomies[$this->options['taxonomy']]) ) {
143	$this->options['taxonomy'] = 'category';
144	}
145
146	} // Default to "category"
147	else {
148	$this->options['taxonomy'] = 'category';
149	}
150
151	if ( isset($this->options['cat']) && !empty($this->options['cat']) ) {
152	$this->options['term_id'] = $this->options['cat'];
153	}
154
155	$term_IDs = explode( ",", $this->options['term_id'] );
156	$in_term_IDs = array();
157	$out_term_IDs = array();
158
159	foreach( $term_IDs as $term_ID ) {
160
161	if ( $term_ID >= 0 )
162	$in_term_IDs[] = trim( $term_ID );
163	else
164	$out_term_IDs[] = trim( $term_ID ) * -1;
165
166	}
167
168	if ( !empty($in_term_IDs) ) {
169
170	$where .= " AND p.ID IN (
171	SELECT object_id
172	FROM `{$wpdb->term_relationships}` AS r
173	JOIN `{$wpdb->term_taxonomy}` AS x ON x.term_taxonomy_id = r.term_taxonomy_id
174	WHERE x.taxonomy = '{$this->options['taxonomy']}'";
175
176	$inTID = '';
177
178	foreach( $in_term_IDs as $in_term_ID ) {
179	$inTID .= "%d, ";
180	array_push( $args, $in_term_ID );
181	}
182
183	$where .= " AND x.term_id IN(" . rtrim($inTID, ", ") . ") )";
184
185	}
186
187	if ( !empty($out_term_IDs) ) {
188
189	$post_ids = get_posts(
190	array(
191	'posts_per_page' => -1,
192	'tax_query' => array(
193	array(
194	'taxonomy' => $this->options['taxonomy'],
195	'field' => 'id',
196	'terms' => $out_term_IDs,
197	),
198	),
199	'fields' => 'ids'
200	)
201	);
202
203	if ( is_array($post_ids) && !empty($post_ids) ) {
204
205	if ( isset($this->options['pid']) && !empty($this->options['pid']) ) {
206	$this->options['pid'] .= "," . implode( ",", $post_ids );
207	}
208	else {
209	$this->options['pid'] = implode( ",", $post_ids );
210	}
211
212	}
213
214	}
215
216	}
217
218	// Exclude these entries from the listing
219	if ( isset($this->options['pid']) && !empty($this->options['pid']) ) {
220
221	$excluded_post_IDs = explode( ",", $this->options['pid'] );
222	$xpid = '';
223	$where .= " AND p.ID NOT IN(";
224
225	foreach( $excluded_post_IDs as $excluded_post_ID ) {
226	$xpid .= "%d, ";
227	array_push( $args, trim($excluded_post_ID) );
228	}
229
230	$where .= rtrim($xpid, ", ") . ")";
231
232	}
233
234	// All-time range
235	if ( "all" == $this->options['range'] ) {
236
237	// Order by views count
238	if ( "comments" != $this->options['order_by'] ) {
239
240	$table = "`{$wpdb->prefix}popularpostsdata` v";
241	$join = "LEFT JOIN `{$wpdb->posts}` p ON v.postid = p.ID";
242
243	// Order by views
244	if ( "views" == $this->options['order_by'] ) {
245
246	if ( !isset($this->options['stats_tag']['views']) \|\| $this->options['stats_tag']['views'] ) {
247	$fields .= ", v.pageviews";
248	}
249
250	$orderby = "ORDER BY v.pageviews DESC";
251
252	}
253	// Order by average views
254	else {
255
256	$now = current_time( 'mysql' );
257
258	$fields .= ", ( v.pageviews/(IF ( DATEDIFF('{$now}', MIN(v.day)) > 0, DATEDIFF('{$now}', MIN(v.day)), 1) ) ) AS avg_views";
259	$groupby = "GROUP BY v.postid";
260	$orderby = "ORDER BY avg_views DESC";
261
262	}
263
264	// Display comments count, too
265	if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
266	$fields .= ", p.comment_count";
267	}
268
269	}
270	// Order by comments count
271	else {
272
273	$table = "`{$wpdb->posts}` p";
274	$where .= " AND p.comment_count > 0";
275	$orderby = "ORDER BY p.comment_count DESC";
276
277	// Display comment count
278	if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
279	$fields .= ", p.comment_count";
280	}
281
282	// Display views count, too
283	if ( isset($this->options['stats_tag']['views']) && $this->options['stats_tag']['views'] ) {
284	$fields .= ", IFNULL(v.pageviews, 0) AS pageviews";
285	$join = "LEFT JOIN `{$wpdb->prefix}popularpostsdata` v ON p.ID = v.postid";
286	}
287
288	}
289
290	}
291	// Custom time range
292	else {
293
294	$now = current_time( 'mysql' );
295
296	// Determine time range
297	switch( $this->options['range'] ){
298	case "last24hours":
299	case "daily":
300	$interval = "24 HOUR";
301	break;
302
303	case "last7days":
304	case "weekly":
305	$interval = "6 DAY";
306	break;
307
308	case "last30days":
309	case "monthly":
310	$interval = "29 DAY";
311	break;
312
313	case "custom":
314	$time_units = array( "MINUTE", "HOUR", "DAY", "WEEK", "MONTH" );
315	$interval = "24 HOUR";
316
317	// Valid time unit
318	if (
319	isset( $this->options['time_unit'] )
320	&& in_array( strtoupper( $this->options['time_unit'] ), $time_units )
321	&& isset( $this->options['time_quantity'] )
322	&& filter_var( $this->options['time_quantity'], FILTER_VALIDATE_INT )
323	&& $this->options['time_quantity'] > 0
324	) {
325	$interval = "{$this->options['time_quantity']} " . strtoupper( $this->options['time_unit'] );
326	}
327
328	break;
329
330	default:
331	$interval = "24 HOUR";
332	break;
333	}
334
335	// Get entries published within the specified time range
336	if ( isset($this->options['freshness']) && $this->options['freshness'] ) {
337	$where .= " AND p.post_date > DATE_SUB('{$now}', INTERVAL {$interval})";
338	}
339
340	// Order by views count
341	if ( "comments" != $this->options['order_by'] ) {
342
343	$table = "`{$wpdb->prefix}popularpostssummary` v";
344	$join = "LEFT JOIN `{$wpdb->posts}` p ON v.postid = p.ID";
345	$where .= " AND v.last_viewed > DATE_SUB('{$now}', INTERVAL {$interval})";
346	$groupby = "GROUP BY v.postid";
347
348	// Order by views
349	if ( "views" == $this->options['order_by'] ) {
350
351	if ( !isset($this->options['stats_tag']['views']) \|\| $this->options['stats_tag']['views'] ) {
352	$fields .= ", SUM(v.pageviews) AS pageviews";
353	$orderby = "ORDER BY pageviews DESC";
354	}
355	else {
356	$orderby = "ORDER BY SUM(v.pageviews) DESC";
357	}
358
359	}
360	// Order by average views
361	else {
362	$fields .= ", ( SUM(v.pageviews)/(IF ( DATEDIFF('{$now}', DATE_SUB('{$now}', INTERVAL {$interval})) > 0, DATEDIFF('{$now}', DATE_SUB('{$now}', INTERVAL {$interval})), 1) ) ) AS avg_views";
363	$orderby = "ORDER BY avg_views DESC";
364	}
365
366	// Display comments count, too
367	if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
368	$fields .= ", IFNULL(c.comment_count, 0) AS comment_count";
369	$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";
370	}
371
372	}
373	// Order by comments count
374	else {
375
376	$table = "`{$wpdb->comments}` c";
377	$join = "LEFT JOIN {$wpdb->posts} p ON c.comment_post_ID = p.ID";
378	$where .= " AND c.comment_date_gmt > DATE_SUB('{$now}', INTERVAL {$interval}) AND c.comment_approved = 1";
379	$groupby = "GROUP BY c.comment_post_ID";
380
381	// Display comment count
382	if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
383	$fields .= ", COUNT(c.comment_post_ID) AS comment_count";
384	$orderby = "ORDER BY comment_count DESC";
385	}
386	else {
387	$orderby = "ORDER BY COUNT(c.comment_post_ID) DESC";
388	}
389
390	// Display views count, too
391	if ( isset($this->options['stats_tag']['views']) && $this->options['stats_tag']['views'] ) {
392	$fields .= ", IFNULL(v.pageviews, 0) AS pageviews";
393	$join .= " LEFT JOIN (SELECT postid, SUM(pageviews) AS pageviews FROM `{$wpdb->prefix}popularpostssummary` WHERE last_viewed > DATE_SUB('{$now}', INTERVAL {$interval}) GROUP BY postid) v ON p.ID = v.postid";
394	}
395
396	}
397
398	}
399
400	// List only published, non password-protected posts
401	$where .= " AND p.post_password = '' AND p.post_status = 'publish'";
402
403	$fields = apply_filters( 'wpp_query_fields', $fields, $this->options );
404	$table = apply_filters( 'wpp_query_table', $table, $this->options );
405	$join = apply_filters( 'wpp_query_join', $join, $this->options );
406	$where = apply_filters( 'wpp_query_where', $where, $this->options );
407	$groupby = apply_filters( 'wpp_query_group_by', $groupby, $this->options );
408	$orderby = apply_filters( 'wpp_query_order_by', $orderby, $this->options );
409	$limit = apply_filters( 'wpp_query_limit', $limit, $this->options );
410
411	// Finally, build the query
412	$query = "SELECT {$fields} FROM {$table} {$join} {$where} {$groupby} {$orderby} {$limit};";
413	$this->query = ( !empty($args) && !has_filter('wpp_query_where') ) ? $wpdb->prepare( $query, $args ) : $query;
414
415	}
416
417	}
418
419	/*
420	* Queries the database.
421	*
422	* @since 4.0.0
423	* @access private
424	*/
425	private function run_query(){
426
427	/*
428	* @var wpdb $wpdb
429	*/
430	global $wpdb;
431
432	if ( isset($wpdb) && !empty($this->query) && !is_wp_error($this->query) ) {
433	$this->posts = $wpdb->get_results( $this->query );
434	}
435
436	}
437
438	/*
439	* Returns the query string.
440	*
441	* @since 4.0.0
442	* @return WP_Error\|string Query string on success, WP_Error on failure
443	*/
444	public function get_query(){
445	return $this->query;
446	}
447
448	/*
449	* Returns the list of posts.
450	*
451	* @since 4.0.0
452	* @return array
453	*/
454	public function get_posts(){
455	return $this->posts;
456	}
457
458	} // end WPP_Query class
459