Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have 3 categories with each 15 posts, I want to do ONE query to the db bringing only 5 first posts for each category, how can I do it?

$q = new WP_Query(array( 'post__in' => array(2,4,8), 'posts_per_page' => **FIRST_5_OF_EACH_CAT** ));

In case its not possible, what is more efficient, getting all the posts for the parent category and looping through them or creating 3 different queries?

share|improve this question
    
How do you want them to be ordered? –  MikeSchinkel Aug 25 '10 at 21:05
    
recently published.. so most recent 5 of category A, most recent stuff of Category B etc.. –  Amit Aug 25 '10 at 21:09
    
Okay, see my answer below –  MikeSchinkel Aug 25 '10 at 23:12

4 Answers 4

up vote 8 down vote accepted

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] = $posts;
    }
  }
  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?

share|improve this answer
1  
Caching via transients is a nice thing to reduce impact on the site. –  hakre Aug 25 '10 at 23:01
    
@hakre - Thanks! –  MikeSchinkel Aug 25 '10 at 23:12
1  
@Mike great idea on using the Transients. –  Chris_O Aug 26 '10 at 0:41
    
@Chris_O - Thanks! –  MikeSchinkel Aug 26 '10 at 0:59
    
@Mike, if I was living in your continent I would have taken classes with you! thanks again! –  Amit Aug 26 '10 at 14:13

Awesome Answer from Mike! Very helpful.. thanks..

One small tweak for the transients api example:

foreach($posts as $post) {
  $category_posts[$post->ID] = $post; // was posts
share|improve this answer

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.

share|improve this answer
    
isn't that 3 hits to the db? I wanted to know if you can do it in 1 hit cause I thought it's more efficient. –  Amit Aug 25 '10 at 20:56
    
well that's what the db is for, right? query the data from it. the db is made for such stuff. it probably is faster then running a complicated sql query you're asking for. don't fear to use the stuff. if it breaks your site, report it here. –  hakre Aug 25 '10 at 21:05
    
mm.. got it, I don't know much about php/mysql/db efficiency (would like to read more), was sure less hits is better and then parse the result myself. –  Amit Aug 25 '10 at 21:11
    
well, true, generally more is more and less is less. But try and test first. The "badier" your software is, the more potential it has for optimization. So better learn by doing, because in the end you will write better software faster and better write faster software. –  hakre Aug 25 '10 at 21:15

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.