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

3 comments

1. 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("nnUNIONnn",$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?

   Log in to Reply

2. WP_Query() does not support something like First X For Each Cat. Instead of WP_Query() you can use get_posts() on each of your categories, so to say three times:

   <?php
   $posts      = array():
   $categories = array(2,4,8);
   foreach($categories as $cat) {
     $posts[] = get_posts('numberposts=5&offset=1&category='.$cat);
   }
   ?>
   

   $posts now contains the first five posts for each category.

   Log in to Reply

3. I don’t know of a way to get the first five posts for each of the categories in a single query. If you’re ever going to only have 45 posts, then hitting the database once and getting your five posts for each category is probably the most efficient approach. Hitting the database three times and combining the results isn’t a bad thing though.

   Log in to Reply