What is the most efficient way of querying posts based on visits and date for current day?

Question

I am about to develop a piece of code for my site to display the most popular posts of the day that are X days old. I would like some help from you to achieve this the most efficient way.

This is what I plan to do, please tell me if there is a more efficient way of doing it.

1. Save each post view in a post meta that will exist for the current day only. I have jetpack enabled so I can use it to get the views for each post.

2. Query posts (with WP_query) by post meta containing the visits for the day.

3. Apply filter by date (to get posts that are X days old). So I can choose how old the posts have to be. For example, I can show the most popular posts that are at most one month old. Posts older than one month will not be taken into account for the query.

4. Save the query in a transient that is going to be updated every hour (so the query wont be called everytime I have to show the popular posts). I want this to be every hour because I want to show visitors new content every hour.

5. The next day, when the post is visited by a user, the post meta will be removed and a new one will be created for the current day.

Note: I have hundreds of thousands of posts.

If I am missing something, please let me know.

Answer 1

I think that you can improve performance

• saving view information in one single option instead in post meta
• applying the filter date on saving instead of on retrieving
• create the markup and save in a transient instead of saving the query (fragment caching)

Rough implementation in a class

class PopularPosts
{

  const OPT = 'myplugin_popular_posts';

  /**
   * After checking post date to see if save or not, increment a counter in a option
   * as an array where keys are post ids and values number of views
   */
  public function save(WP_Post $post)
  {
    if ( ! $this->shouldSave($post))
        return;
    $opt = get_option(self::OPT, array());
    if ( ! isset($opt[$post->ID]) ) {
       $opt[$post->ID] = 0;
    }
    $opt[$post->ID]++;
    update_option(self::OPT, $opt);
  }


  /**
   * Get markup from cache or rebuild it.
   * In latter case setup an action on shutdown to defer cache of the markup.
   * @return string
   */
  public static function getMarkup()
  {
    $key = self::OPT . date('Yz'); // change everyday
    $cached = get_transient($key);
    if ( ! empty($cached))
        return $cached; // if cache is available just output it

    $instance = new static;
    $markup = $instance->buildMarkup();
    if (empty($markup))
        return ''; // return without cache empty results

    add_action('shutdown', function() use($markup,$key) {
      set_transient($key, $markup, HOUR_IN_SECONDS);
    });

    return $markup;
  }


  /**
   * Get popular posts and return proper markup
   * @return string
   */
  public function buildMarkup()
  {
    $opt = get_option(self::OPT);
    if(empty($opt))
        return; // nothing to get and show

    $posts = $this->getPosts($opt); // obtain posts
    $out = '';

    $format = '<li><a href="%s">%s</a><span>(%d %s)</span></li>';

    foreach($posts as $post) { 
      $title = apply_filters('the_title', $post->post_title);
      $plink = get_permalink($post);
      $out .= sprintf($format, $plink, $title, $opt[$post->ID], __('views', 'txdmn'));
    }

    return '<ul class="popular_posts">' . $out . '</ul>';
  }


  /**
   * Return true if the posts is not older than X days, where X is filterable
   * @return boolean
   */
  private function shouldSave( WP_Post $post )
  {
    $max_old = apply_filters( 'myplugin_popular_posts_max_days', 31 );
    $ptime = DateTime::createFromFormat('Y-m-d H:i:s', $post->post_date);
    $now = new DateTime('now');

    return (int) $now->diff($ptime)->days <= (int) $max_old;
  }


  /**
   * Return X popular posts, where X number is filterable
   * @return array
   */
  private function getPosts($opt)
  {
    arsort($opt); // reverse order: more to less popular
    $num = apply_filters('myplugin_popular_posts_num', 5);
    $ids = array_keys(array_slice($opt, 0, $num));

    return (array) get_posts(array('post__in' => $ids));
  }

}

Usage

To update posts views counter:

add_action('shutdown', function() {
  if (is_single()) { // maybe check for post type using is_singular()
    $popularposts = new PopularPosts;
    $popularposts->save(get_queried_object());
  }     
});

And to show markup in your templates:

<?= PopularPosts::getMarkup() ?>

Gotchas and possible improvements

The counter option should be reset evey day, code for that is not provided here, but should be pretty easy to implement.

Using a lib like WP-TLC-Transient you can improve getMarkup method using soft expiration and background updating. Something like this:

  public static function getMarkup()
  {
    $key = self::OPT . date('Yz'); // change everyday

    return tlc_transient( $key )
      ->updates_with(array(new static, 'buildMarkup'))
      ->expires_in(HOUR_IN_SECONDS)
      ->get();
  }

Note that I'm not familiar with Jetpack, so I don't know how get post stats from it. For sake of semplicity I used an hook on 'shutdown' to just increment the counter in the option. That's not ideal because in that way counter is incremented even if an user just refresh the page... of course you can improve it.

Please note

Code is completely untested.