Using WP_Query to Query Multiple Categories with Limited Posts Per Category?

by

What you want is possible but will require you to delve into SQL which I like to avoid whenever possible (not because I don’t know it, I’m an advance SQL developer, but because in WordPress you want to use the API whenever possible to minimize future compatibility problems related to future potential database structure changes.)

SQL with a UNION Operator is a Possibility

To use SQL what you need is a UNION operator in your query, something like this assuming your category slugs are "category-1", "category-1" and "category-3":

SELECT * FROM wp_posts WHERE ID IN (
  SELECT tr.object_id
  FROM wp_terms t 
  INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
  INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
  WHERE tt.taxonomy='category' AND t.slug='category-1'
  LIMIT 5

  UNION

  SELECT tr.object_id
  FROM wp_terms t 
  INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
  INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
  WHERE tt.taxonomy='category' AND t.slug='category-2'
  LIMIT 5

  UNION

  SELECT tr.object_id
  FROM wp_terms t 
  INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
  INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
  WHERE tt.taxonomy='category' AND t.slug='category-3'
  LIMIT 5
)

You can use SQL UNION with a posts_join Filter

Using the above you can either just make the call directly or you can use a posts_join filter hook like as follows; note I’m using a PHP heredoc so be sure the SQL; is flush left. Also note I used a global var to allow you to define the categories outside of the hook by listing the category slugs in an array. You can put this code in a plugin or in your theme’s functions.php file:

<?php
global $top_5_for_each_category_join;
$top_5_for_each_category_join = array('category-1','category-2','category-3');
add_filter('posts_join','top_5_for_each_category_join',10,2);
function top_5_for_each_category_join($join,$query) {
  global $top_5_for_each_category_join;
  $unioned_selects = array();
  foreach($top_5_for_each_category_join as $category) {
    $unioned_selects[] =<<<SQL
SELECT object_id
FROM wp_terms t
INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
WHERE tt.taxonomy='category' AND t.slug='{$category}'
LIMIT 5
SQL;
  }
  $unioned_selects = implode("\n\nUNION\n\n",$unioned_selects);
  return $join . " INNER JOIN ($unioned_selects) categories ON wp_posts.ID=categories.object_id " ;
}

But There Can Be Side-effects

Of course using the query modification hooks like posts_join always invites side-effects in that they act globally on queries and thus you usually need to wrap your modifications in an if that only uses it when needed and what criteria to test for can be tricky.

Focus on Optimization Instead?

However, I assume your question is concerned is more about optimization than about being able to do a top 5 time 3 query, right? If that is the case then maybe there are other options that use the WordPress API?

Better to Use Transients API for Caching?

I assume your posts won’t change that often, correct? What if you accept the three (3) query hit periodically and then cache the results using the Transients API? You’ll get maintainable code and great performance; a good bit better than the UNION query above because WordPress will store the lists of posts as a serialized array in one record of the wp_options table.

You can take the following example and drop into your web site’s root as test.php to test this out:

<?php

$timeout = 4; // 4 hours
$categories = array('category-1','category-2','category-3');

include "wp-load.php";
$category_posts = get_transient('top5_posts_per_category');
if (!is_array($category_posts) || count($category_posts)==0) {
  echo "Hello Every {$timeout} hours!";
  $category_posts = array();
  foreach($categories as $category) {
    $posts = get_posts("post_type=post&numberposts=5&taxonomy=category&term={$category}");
    foreach($posts as $post) {
      $category_posts[$post->ID] = $post;
    }
  }
  set_transient('top5_posts_per_category',$category_posts,60*60*$timeout);
}
header('Content-Type:text/plain');
print_r($category_posts);

Summary

While yes you can do what you asked for using a SQL UNION query and the posts_join filter hook you are probably better offer using caching with the Transients API instead.

Hope this helps?

Leave a Comment