<?php
/**
* Queries the database for popular posts.
*
* To use this class, you must pass it an array of parameters (mostly the same ones used with
* the wpp_get_mostpopular() template tag).
*
* eg.:
* $popular_posts = new \WordPressPopularPosts\Query([
* 'range' => 'last7days',
* 'order_by' => 'views',
* 'limit' => 5
* ]);
* var_dump( $popular_posts->get_posts() );
*
* @since 4.0.0
* @package WordPressPopularPosts
*/
namespace WordPressPopularPosts;
class Query {
/**
* Database query string.
*
* @since 4.0.0
* @access private
* @var string $query
*/
private $query;
/**
* List of posts.
*
* @since 4.0.0
* @access private
* @var array $posts
*/
private $posts = [];
/**
* Plugin options.
*
* @since 4.0.0
* @access private
* @var array $options
*/
private $options;
/**
* Constructor.
*
* @since 4.0.0
* @param array $options
*/
public function __construct(array $options = [])
{
$this->set_options($options)
->execute();
}
/**
* Sets class options.
*
* @since 5.0.0
* @param array $options
* @return WordPressPopularPosts\Query
*/
public function set_options(array $options = [])
{
$this->options = $options;
// Reset posts array
$this->posts = [];
return $this;
}
/**
* Builds the database query.
*
* @since 4.0.0
* @access private
*/
private function build_query()
{
/**
* @var wpdb $wpdb
*/
global $wpdb;
if ( isset($wpdb) ) {
$this->options = Helper::merge_array_r(
Settings::get('widget_options'),
(array) $this->options
);
$now = new \DateTime(Helper::now(), wp_timezone());
$args = [];
$fields = 'p.ID AS id, p.post_title AS title, p.post_author AS uid';
$table = '';
$join = '';
$where = 'WHERE 1 = 1';
$groupby = '';
$orderby = '';
$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']}" : '');
// Get post date
if ( isset($this->options['stats_tag']['date']['active']) && $this->options['stats_tag']['date']['active'] ) {
$fields .= ', p.post_date AS date';
}
// Get post excerpt $instance
if ( isset($this->options['post-excerpt']['active']) && $this->options['post-excerpt']['active'] ) {
$fields .= ', p.post_excerpt AS post_excerpt, p.post_content AS post_content';
}
// Get entries from these post types
$post_types = ['post', 'page'];
if ( isset($this->options['post_type']) && ! empty($this->options['post_type']) ) {
$post_types = explode(',', $this->options['post_type']);
$pt = '';
$where .= ' AND p.post_type IN(';
foreach( $post_types as $post_type ) {
$pt .= '%s, ';
array_push($args, trim($post_type));
}
$where .= rtrim($pt, ', ') . ')';
}
else {
$where .= " AND p.post_type IN('post', 'page')";
}
// Get entries from these authors
if ( isset($this->options['author']) && ! empty($this->options['author']) ) {
$author_IDs = explode(',', $this->options['author']);
$uid = '';
$where .= ' AND p.post_author IN(';
foreach( $author_IDs as $author_ID ) {
$uid .= '%d, ';
array_push($args, trim($author_ID));
}
$where .= rtrim($uid, ', ') . ')';
}
// Get / exclude entries from these taxonomies
if (
( isset($this->options['taxonomy']) && ! empty($this->options['taxonomy']) ) &&
( ( isset($this->options['cat']) && ! empty($this->options['cat']) )
|| ( isset($this->options['term_id']) && ! empty($this->options['term_id']) ) )
) {
if ( isset($this->options['cat']) && ! empty($this->options['cat']) ) {
$this->options['term_id'] = $this->options['cat'];
}
// Let's do some cleanup before attempting the filtering
$this->options['taxonomy'] = trim($this->options['taxonomy'], ' ;');
$this->options['term_id'] = trim($this->options['term_id'], ' ;');
if (
$this->options['taxonomy']
&& $this->options['term_id']
) {
$taxonomies = array_map('trim', explode(';', $this->options['taxonomy']));
$term_IDs_for_taxonomies = array_map('trim', explode(';', $this->options['term_id']));
// Apparently we have at least one taxonomy and matching term ID(s)
if ( count($taxonomies) && count($term_IDs_for_taxonomies) ) {
// Parameters mismatch: we either have too little taxonomies
// or too little term ID groups, let's trim the excess
if ( count($taxonomies) != count($term_IDs_for_taxonomies) ) {
// We have more taxonomies than term ID groups,
// let's remove some taxonomies
if ( count($taxonomies) > count($term_IDs_for_taxonomies) ) {
$taxonomies = array_slice($taxonomies, 0, count($term_IDs_for_taxonomies));
}
// We have more term ID groups than taxonomies,
// let's remove some term ID groups
else {
$term_IDs_for_taxonomies = array_slice($term_IDs_for_taxonomies, 0, count($taxonomies));
}
}
$registered_taxonomies = get_taxonomies(['public' => true]);
foreach ( $taxonomies as $index => $taxonomy ) {
// Invalid taxonomy, discard the taxonomy
if ( ! isset($registered_taxonomies[$taxonomy]) ) {
unset($taxonomies[$index]);
unset($term_IDs_for_taxonomies[$index]);
}
}
// If we still have something we can use
// for filtering, let's use it
if (
! empty($taxonomies)
&& ! empty($term_IDs_for_taxonomies)
) {
$tax_terms = [];
foreach( $taxonomies as $taxIndex => $taxonomy ) {
$tax_terms[$taxonomy] = [
'term_id_include' => [],
'term_id_exclude' => []
];
$term_IDs = array_map('trim', explode(',', $term_IDs_for_taxonomies[$taxIndex]));
foreach ( $term_IDs as $term_ID ) {
if ( $term_ID >= 0 ) {
$tax_terms[$taxonomy]['term_id_include'][] = (int) $term_ID;
} else {
$tax_terms[$taxonomy]['term_id_exclude'][] = abs((int) $term_ID);
}
}
}
$term_rel_table = $wpdb->prepare("%i", "{$wpdb->term_relationships}");
$term_tax_table = $wpdb->prepare("%i", "{$wpdb->term_taxonomy}");
foreach( $tax_terms as $taxonomy => $terms ) {
if ( ! empty($terms['term_id_include']) ) {
$where .= " AND p.ID IN (
SELECT object_id
FROM {$term_rel_table} AS r
JOIN {$term_tax_table} AS x ON x.term_taxonomy_id = r.term_taxonomy_id
WHERE x.taxonomy = %s";
array_push($args, $taxonomy);
$inTID = '';
foreach ($terms['term_id_include'] as $term_ID) {
$inTID .= '%d, ';
array_push($args, $term_ID);
}
$where .= ' AND x.term_id IN(' . rtrim($inTID, ', ') . ') )';
}
if ( ! empty($terms['term_id_exclude']) ) {
//phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_tax_query
$post_ids = get_posts(
[
'post_type' => $post_types,
'posts_per_page' => -1,
'tax_query' => [
[
'taxonomy' => $taxonomy,
'field' => 'id',
'terms' => $terms['term_id_exclude'],
],
],
'fields' => 'ids'
]
);
//phpcs:enable
if ( is_array($post_ids) && ! empty($post_ids) ) {
if ( isset($this->options['exclude']) && ! empty($this->options['exclude']) ) {
$pid_arr = explode(',', $this->options['exclude']);
$pid_arr = array_merge($pid_arr, $post_ids);
// Remove duplicates
$pid_arr_no_dupes = [];
foreach( $pid_arr as $key => $post_id ) {
$pid_arr_no_dupes[$post_id] = true;
}
$pid_arr_no_dupes = array_keys($pid_arr_no_dupes);
$this->options['exclude'] = implode(',', $pid_arr_no_dupes);
} else {
$this->options['exclude'] = implode(',', $post_ids);
}
}
}
}
}
}
}
}
// Exclude these entries from the listing
if ( isset($this->options['exclude']) && ! empty($this->options['exclude']) ) {
$excluded_post_IDs = explode(',', $this->options['exclude']);
$xpid = '';
$where .= ' AND p.ID NOT IN(';
foreach( $excluded_post_IDs as $excluded_post_ID ) {
$xpid .= '%d, ';
array_push($args, trim($excluded_post_ID));
}
$where .= rtrim($xpid, ', ') . ')';
}
$table = $wpdb->prepare("%i", "{$wpdb->posts}") . " p";
$comments_table = $wpdb->prepare("%i", "{$wpdb->comments}");
$data_table = $wpdb->prepare("%i", "{$wpdb->prefix}popularpostsdata");
$summary_table = $wpdb->prepare("%i", "{$wpdb->prefix}popularpostssummary");
// All-time range
if ( 'all' == $this->options['range'] ) {
// Order by views count
if ( 'comments' != $this->options['order_by'] ) {
$join = "INNER JOIN {$data_table} v ON p.ID = v.postid";
// Order by views
if ( 'views' == $this->options['order_by'] ) {
if ( ! isset($this->options['stats_tag']['views']) || $this->options['stats_tag']['views'] ) {
$fields .= ', v.pageviews';
}
$orderby = 'ORDER BY v.pageviews DESC';
}
// Order by average views
else {
$fields .= ", ( v.pageviews/(IF ( DATEDIFF('{$now->format('Y-m-d')}', MIN(v.day)) > 0, DATEDIFF('{$now->format('Y-m-d')}', MIN(v.day)), 1) ) ) AS avg_views";
$groupby = 'GROUP BY v.postid';
$orderby = 'ORDER BY avg_views DESC';
}
// Display comments count, too
if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
$fields .= ', p.comment_count';
}
}
// Order by comments count
else {
$where .= ' AND p.comment_count > 0';
$orderby = 'ORDER BY p.comment_count DESC';
// Display comment count
if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
$fields .= ', p.comment_count';
}
// Display views count, too
if ( isset($this->options['stats_tag']['views']) && $this->options['stats_tag']['views'] ) {
$fields .= ', IFNULL(v.pageviews, 0) AS pageviews';
$join = "INNER JOIN {$data_table} v ON p.ID = v.postid";
}
}
}
// Custom time range
else {
$start_date = clone $now;
// Determine time range
switch( $this->options['range'] ){
case 'last24hours':
case 'daily':
$start_date = $start_date->sub(new \DateInterval('P1D'));
$start_datetime = $start_date->format('Y-m-d H:i:s');
$views_time_range = "view_datetime >= '{$start_datetime}'";
break;
case 'last7days':
case 'weekly':
$start_date = $start_date->sub(new \DateInterval('P6D'));
$start_datetime = $start_date->format('Y-m-d');
$views_time_range = "view_date >= '{$start_datetime}'";
break;
case 'last30days':
case 'monthly':
$start_date = $start_date->sub(new \DateInterval('P29D'));
$start_datetime = $start_date->format('Y-m-d');
$views_time_range = "view_date >= '{$start_datetime}'";
break;
case 'custom':
$time_units = ['MINUTE', 'HOUR', 'DAY', 'WEEK', 'MONTH'];
// Valid time unit
if (
isset($this->options['time_unit'])
&& in_array(strtoupper($this->options['time_unit']), $time_units)
&& isset($this->options['time_quantity'])
&& filter_var($this->options['time_quantity'], FILTER_VALIDATE_INT)
&& $this->options['time_quantity'] > 0
) {
$time_quantity = $this->options['time_quantity'];
$time_unit = strtoupper($this->options['time_unit']);
if ( 'MINUTE' == $time_unit ) {
$start_date = $start_date->sub(new \DateInterval('PT' . (60 * $time_quantity) . 'S'));
$start_datetime = $start_date->format('Y-m-d H:i:s');
$views_time_range = "view_datetime >= '{$start_datetime}'";
} elseif ( 'HOUR' == $time_unit ) {
$start_date = $start_date->sub(new \DateInterval('PT' . ((60 * $time_quantity) - 1) . 'M59S'));
$start_datetime = $start_date->format('Y-m-d H:i:s');
$views_time_range = "view_datetime >= '{$start_datetime}'";
} elseif ( 'DAY' == $time_unit ) {
$start_date = $start_date->sub(new \DateInterval('P' . ($time_quantity - 1) . 'D'));
$start_datetime = $start_date->format('Y-m-d');
$views_time_range = "view_date >= '{$start_datetime}'";
} elseif ( 'WEEK' == $time_unit ) {
$start_date = $start_date->sub(new \DateInterval('P' . ((7 * $time_quantity) - 1) . 'D'));
$start_datetime = $start_date->format('Y-m-d');
$views_time_range = "view_date >= '{$start_datetime}'";
} else {
$start_date = $start_date->sub(new \DateInterval('P' . ((30 * $time_quantity) - 1) . 'D'));
$start_datetime = $start_date->format('Y-m-d');
$views_time_range = "view_date >= '{$start_datetime}'";
}
} // Invalid time unit, default to last 24 hours
else {
$start_date = $start_date->sub(new \DateInterval('P1D'));
$start_datetime = $start_date->format('Y-m-d H:i:s');
$views_time_range = "view_datetime >= '{$start_datetime}'";
}
break;
default:
$start_date = $start_date->sub(new \DateInterval('P1D'));
$start_datetime = $start_date->format('Y-m-d H:i:s');
$views_time_range = "view_datetime >= '{$start_datetime}'";
break;
}
// Get entries published within the specified time range
if ( isset($this->options['freshness']) && $this->options['freshness'] ) {
$where .= " AND p.post_date >= '{$start_datetime}'";
}
// Order by views count
if ( 'comments' != $this->options['order_by'] ) {
// Order by views
if ( 'views' == $this->options['order_by'] ) {
$fields .= ', v.pageviews';
$join = "INNER JOIN (SELECT SUM(pageviews) AS pageviews, postid FROM {$summary_table} WHERE {$views_time_range} GROUP BY postid) v ON p.ID = v.postid";
$orderby = 'ORDER BY pageviews DESC';
}
// Order by average views
else {
$fields .= ', v.avg_views';
$join = "INNER JOIN (SELECT SUM(pageviews)/(IF ( DATEDIFF('{$now->format('Y-m-d H:i:s')}', '{$start_datetime}') > 0, DATEDIFF('{$now->format('Y-m-d H:i:s')}', '{$start_datetime}'), 1) ) AS avg_views, postid FROM {$summary_table} WHERE {$views_time_range} GROUP BY postid) v ON p.ID = v.postid";
$orderby = 'ORDER BY avg_views DESC';
}
// Display comments count, too
if ( isset($this->options['stats_tag']['comment_count']) && $this->options['stats_tag']['comment_count'] ) {
$fields .= ', IFNULL(c.comment_count, 0) AS comment_count';
$join .= " LEFT JOIN (SELECT comment_post_ID, COUNT(comment_post_ID) AS comment_count FROM {$comments_table} WHERE comment_date_gmt >= '{$start_datetime}' AND comment_approved = '1' GROUP BY comment_post_ID) c ON p.ID = c.comment_post_ID";
}
}
// Order by comments count
else {
$fields .= ', c.comment_count';
$join = "INNER JOIN (SELECT COUNT(comment_post_ID) AS comment_count, comment_post_ID FROM {$comments_table} WHERE comment_date_gmt >= '{$start_datetime}' AND comment_approved = '1' GROUP BY comment_post_ID) c ON p.ID = c.comment_post_ID";
$orderby = 'ORDER BY comment_count DESC';
// Display views count, too
if ( isset($this->options['stats_tag']['views']) && $this->options['stats_tag']['views'] ) {
$fields .= ', v.pageviews';
$join .= " INNER JOIN (SELECT SUM(pageviews) AS pageviews, postid FROM {$summary_table} WHERE {$views_time_range} GROUP BY postid) v ON p.ID = v.postid";
}
}
}
// List only published, non password-protected posts
$where .= " AND p.post_password = '' AND p.post_status = 'publish'";
if ( ! empty($args) ) {
$where = $wpdb->prepare($where, $args); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $where and $args are built and prepared dynamically, see above
}
$fields = apply_filters('wpp_query_fields', $fields, $this->options);
$table = apply_filters('wpp_query_table', $table, $this->options);
$join = apply_filters('wpp_query_join', $join, $this->options);
$where = apply_filters('wpp_query_where', $where, $this->options);
$groupby = apply_filters('wpp_query_group_by', $groupby, $this->options);
$orderby = apply_filters('wpp_query_order_by', $orderby, $this->options);
$limit = apply_filters('wpp_query_limit', $limit, $this->options);
// Finally, build the query
$query = "SELECT {$fields} FROM {$table} {$join} {$where} {$groupby} {$orderby} {$limit};";
$this->query = $query;
}
}
/**
* Queries the database.
*
* @since 4.0.0
* @access private
*/
private function run_query()
{
/**
* @var wpdb $wpdb
*/
global $wpdb;
if ( isset($wpdb) && ! empty($this->query) && ! is_wp_error($this->query) ) {
$this->posts = $wpdb->get_results($this->query); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- at this point $this->query has been prepared already
}
}
/**
* Executes the query.
*
* @since 6.0.0
* @return WordPressPopularPosts\Query
*/
public function execute()
{
$this->build_query();
$this->run_query();
return $this;
}
/**
* Returns the query string.
*
* @since 4.0.0
* @return WP_Error|string Query string on success, WP_Error on failure
*/
public function get_query()
{
return $this->query;
}
/**
* Returns the list of posts.
*
* @since 4.0.0
* @return array
*/
public function get_posts()
{
return $this->posts;
}
}