Get popular post IDs array

I was just coming here to search for this exact thing. So, I second this!

I need too. I created this custom loop, but does not work.

[ Moderator note: code fixed. Please wrap code in the backtick character or use the code button. ]

<?php
 $querystr = "SELECT p.ID AS 'id' , p.post_title AS 'title' , p.post_date AS 'date' , p.post_author AS 'uid' , v.pageviews AS 'pageviews'
              FROM (SELECT id , SUM(pageviews) AS pageviews , MAX(last_viewed) AS last_viewed
              FROM wp_popularpostssummary WHERE last_viewed > DATE_SUB('2014-09-09 16:45:06' , INTERVAL 1 MONTH)
              GROUP BY id ORDER BY pageviews DESC , last_viewed DESC) v LEFT JOIN wp_posts p ON v.id = p.ID
              WHERE p.post_type IN('post' , 'page') AND p.ID NOT IN ( SELECT object_id
              FROM wp_term_relationships AS r JOIN wp_term_taxonomy AS x ON x.term_taxonomy_id = r.term_taxonomy_id
              JOIN wp_terms AS t ON t.term_id = x.term_id WHERE x.taxonomy = 'cds-completos' AND t.term_id IN(17) ) AND p.post_password = ''
              AND p.post_status = 'publish' LIMIT 14";
 $pageposts = $wpdb->get_results($querystr, OBJECT);
 ?>

<?php if ($pageposts): ?>
 <?php global $post; ?>
 <?php foreach ($pageposts as $post): ?>
 <?php setup_postdata($post); ?>

<?php the_title(); ?>

 <?php endforeach; ?>
 <?php else : ?>
    <h2>Not Found</h2>
 <?php endif; ?>

Well that query is a whole lot of headfuck. Here’s something a lot simpler:

$query = sprintf("SELECT postid from wp_popularpostsdata ORDER BY pageviews DESC LIMIT 4");
$result = mysql_query($query);
if ($result) {
	while ($row = mysql_fetch_assoc($result)) {
		echo "<pre>".print_r($row,1)."</pre>";
	}
	mysql_free_result($result);
}

I ended up doing the following:

1. Build a query (similar to the two previous posts) that grabs the N most popular posts (in descending order).
2. Loop through the results and build an array that is just those IDs in order
3. Pass those ids into a WP_Query as follows:

   $args = array(
   	'order' => 'DESC',
   	'orderby' => 'post__in',
   	'post__in' => $popularIDs, // my array of IDs
   );
   $posts_query = new WP_Query($args);

Then I can do the usual WP Loop fun with the returned posts. The key is keeping them in order by using the proper orderby argument above (post__in).

Andy do you have an example of your code for part 1 and 2 please?

I do. The query-build part is from someone else (and right now my mind is blanking on whether it was a consultant we’d hired or something I’d found online). I stripped it down (it was much longer and complicated) and added in the bit at the end where I build the array of the IDs in order (#2 in my earlier post). It includes the capability of time boxing the results though I currently only use it for all posts on my sites.

function get_popular_post_ids($args) {

	global $wpdb;
	$prefix = $wpdb->prefix . "popularposts";
	$where = "";
	$now = current_time('mysql');

	/* Build the Query */
	$fields = "p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid', v.pageviews AS 'pageviews'";

	$from = "{$prefix}data v LEFT JOIN {$wpdb->posts} p ON v.postid = p.ID";

	switch( $args['range'] ){
		case "yesterday":
			$where .= " AND p.post_date > DATE_SUB('{$now}', INTERVAL 1 DAY) ";
			break;

		case "daily":
			$where .= " AND p.post_date > DATE_SUB('{$now}', INTERVAL 1 DAY) ";
			break;

		case "weekly":
			$where .= " AND p.post_date > DATE_SUB('{$now}', INTERVAL 1 WEEK) ";
			break;

		case "monthly":
			$where .= " AND p.post_date > DATE_SUB('{$now}', INTERVAL 1 MONTH) ";
			break;

		default:
			$where .= "";
			break;
	}

	$where .= " AND p.post_type = 'post'";
	$where .= " AND p.post_password = '' AND p.post_status = 'publish'";	

	$orderby = "ORDER BY pageviews DESC";

	$query = "SELECT {$fields} FROM {$from} {$where} {$orderby};";

	$result = $wpdb->get_results($query);

	$counter = 0;
	$myIDs = array();

	foreach ($result as $aPost) {
		$theID = $aPost->id;

		if ( !$theID == "" ) {
			$myIDs[$counter] = $theID;
			$counter++;
		}
	}

	return($myIDs);
}

Hope this helps!