WordPress Posts by Comment Count

 

Retrieving WordPress Posts by Comment Count

This is a useful PHP utility for retrieving a list of WordPress posts by comment count. This utility can be used in a page or post template, custom theme, or a custom plugin. The function is designed to take a few parameters and build a dynamic SQL query to retrieve the post list. The query is then run against the WordPress database to retrieve the list.

Explanation of Function

This function uses the global WordPress database class (wpdb) and builds a simple MySQL SELECT statement to retrieve a list of post titles in descending ranked order by comment count. The desired date range, number of posts to return, and the option to include pages and posts, are function parameters that are dynamically incorporated into the MySQL query. The function could also easily be modified to accept custom post types. It returns an associative array that uses post_title as the array keys, and comment_count as the element value. The SELECT statement can be modified to retrieve the post_ID, or other post attributes as desired. Basically, anything that the WordPress get_post function can return in the post object.

Function Building Blocks

This section lists all of the core building blocks for the function and a short description. The entire function definition can be found below.

  1. First, build a SQL statement WHERE clause based on input parameters (include_pages, include_posts, & period)
  2. 	if ( $include_pages && $include_posts ) {
    		// WHERE clause for both pages & posts
    		if ( $period === 'Total' ) {
    			// WHERE clause for period = total (simpler query)
    			$WHERE .= "post_type='page' OR post_type='post' ";
    		} else {
    			// WHERE clause for custom date range (more complex query)
    			$WHERE .= "t1.post_type='page' OR t1.post_type='post' ";
    		}
    	} elseif ( $include_pages ) {
    		// WHERE clause for only pages
    		if ( $period === 'Total' ) {
    			// WHERE clause for period = total (simpler query)
    			$WHERE .= "post_type='page' ";
    		} else {
    			// WHERE clause for custom date range (more complex query)
    			$WHERE .= "t1.post_type='page' "; // assumes wp-posts defined as t1
    		}
    	} elseif ( $include_posts ) {
    		// WHERE clause for only posts
    		if ( $period === 'Total' ) {
    			// WHERE clause for period = total (simpler query)
    			$WHERE .= "post_type='post' ";
    		} else {
    			// WHERE clause for custom date range (more complex query)
    			$WHERE .= "t1.post_type='post' "; // assumes wp-posts defined as t1
    		}
    	} elseif ( !$include_posts && !$include_pages ) {
    		// just return empty array if we're not looking for pages or posts
    		return $rows;
    	}
    
  3. Next, insert the WHERE clause into simple SQL statement if period = Total
  4. 	if ( $period === 'Total' ) {
    		// basic query for posts/pages from all dates
    		$my_query = array( "SELECT post_title, comment_count ",
    			"FROM {$wpdb->prefix}posts ",
    			"$WHERE ORDER BY comment_count DESC LIMIT $num_rows"
    			);
    		// finalize the query and execute
    		$posts = $wpdb->get_results( join( '', $my_query ) ); // default return type is an OBJECT
    
  5. Or if we’re working with a custom date range, insert WHERE clause into more complex query
  6. This query requires an INNER JOIN statement for wp-posts and wp-comments on the ID and comment_post_ID columns. Note the inclusion of times for each date to make sure we return all posts published during the specified date range.

    	} else {
    		// more complex query for a selected date range
    		$my_query = array( "SELECT comment_count, post_title ",
    			"FROM {$wpdb->prefix}posts $WHERE AS t1 INNER JOIN {$wpdb->prefix}comments as t2 ON t1.ID = t2.comment_post_ID ",
    			"WHERE t2.comment_date >= '" . $start_date . " 00:00:00' AND t2.comment_date <= '" . $end_date . " 23:59:59' ",
    			"GROUP BY ID ",
    			"ORDER BY comment_count DESC LIMIT $num_rows"
    			);
    		// finalize the query and execute
    
  7. Then, Execute the SQL query
  8. Here we’re joining together an array of strings via the PHP implode() function and then executing.

    		$posts = $wpdb->get_results( implode( '', $my_query ) ); // default return type is an OBJECT
    
  9. Finally, place results into associative array and return
  10. 	// build associative array where post_title => #_of_comments and return
    	foreach ( $posts as $post ) {
    		$rows[$post->post_title] = $post->comment_count;
    	}
    	// return the result set
    	return $rows;
    

Calling the Function

// function defined as method of static object class 'wp_plugin_utilities'
// first, request the list of posts with your desired arguments
$post_list = wp_plugin_utilities::get_posts_by_comment_count(
	$end_date = '2012-01-01',
	$include_pages = false,
	$include_posts = true,
	$num_rows = 20,
	$period = false,
	$start_date = '2011-01-01'
	);
// then output the list, or use it for another purpose...
echo "<p>The top $num_rows posts from $start_date to $end_date by comment count are:</p>";
// loop through each post item, print the title and # of comments
foreach ( $post_list as $post_title => $comment_count ) {
	echo "$post_title comment count: $comment_count<br />";
}

Function Definition

This is the entire function definition, with explanatory comments inserted.

/**
 * 
 * Method to retrieve list of WP post titles with the most comments (descending order).
 * 
 * @param string $end_date (last date of publication for posts to be included in our list, formatted as YYYY-MM-DD)
 * @param string $include_pages (true or false)
 * @param string $include_posts (true or false)
 * @param integer $num_rows (number of post titles we want returned in our list)
 * @param string $period (if we want all posts, set this to 'Total', any other value is ignored)
 * @param string $start_date (first date of publication for posts to be included in our list, formatted as YYYY-MM-DD)
 * @return array $rows (formatted as $rows['post_title'] = post_comment_count, if no results returns empty array)
 */
public function get_posts_by_comment_count( $end_date, $include_pages, $include_posts, $num_rows, $period, $start_date ) {
	global $wpdb; // global WordPress database class
	$rows = array(); // initialize return array
	$WHERE = "WHERE "; // beginning of WHERE clause for SQL statement
	// determine if we're looking for pages & posts, only pages, or only posts
	if ( $include_pages && $include_posts ) {
		// WHERE clause for both pages & posts
		if ( $period === 'Total' ) {
			// WHERE clause for period = total (simpler query)
			$WHERE .= "post_type='page' OR post_type='post' ";
		} else {
			// WHERE clause for custom date range (more complex query)
			$WHERE .= "t1.post_type='page' OR t1.post_type='post' ";
		}
	} elseif ( $include_pages ) {
		// WHERE clause for only pages
		if ( $period === 'Total' ) {
			// WHERE clause for period = total (simpler query)
			$WHERE .= "post_type='page' ";
		} else {
			// WHERE clause for custom date range (more complex query)
			$WHERE .= "t1.post_type='page' ";
		}
	} elseif ( $include_posts ) {
		// WHERE clause for only posts
		if ( $period === 'Total' ) {
			// WHERE clause for period = total (simpler query)
			$WHERE .= "post_type='post' ";
		} else {
			// WHERE clause for custom date range (more complex query)
			$WHERE .= "t1.post_type='post' ";
		}
	} elseif ( !$include_posts && !$include_pages ) {
		// just return empty array if we're not looking for pages or posts
		return $rows;
	}
	// check to see if we want posts from all dates
	if ( $period === 'Total' ) {
		// basic query for posts/pages from all dates
		$my_query = array( "SELECT post_title, comment_count ",
			"FROM {$wpdb->prefix}posts ",
			"$WHERE ORDER BY comment_count DESC LIMIT $num_rows"
			);
		// finalize the query and execute
		$posts = $wpdb->get_results( join( '', $my_query ) ); // default return type is an OBJECT
	// otherwise, we are looking for posts in a specific date range
	} else {
		// more complex query for a selected date range
		$my_query = array( "SELECT comment_count, post_title ",
			"FROM {$wpdb->prefix}posts AS t1 INNER JOIN {$wpdb->prefix}comments as t2 ON t1.ID = t2.comment_post_ID ",
			"$WHERE AND t2.comment_date >= '" . $start_date . " 00:00:00' AND t2.comment_date <= '" . $end_date . " 23:59:59' ",
			"GROUP BY ID ",
			"ORDER BY comment_count DESC LIMIT $num_rows"
			);
		// finalize the query and execute
		$posts = $wpdb->get_results( join( '', $my_query ) ); // default return type is an OBJECT
	}
	// build associative array where post_title => #_of_comments and return
	foreach ( $posts as $post ) {
		$rows[$post->post_title] = $post->comment_count;
	}
	// return the result set
	return $rows;
}