get all categories' latest post in one query

Question

My WP blog has 12 categories, 80 subcategories.

I need to fetch the latest post from each of those 12 categories.

I know how to do it in 12 queries using 12 get_posts() but is there a way to get them in one query?

I tried to make it work like this but it fetches more than 12 posts:

// Get array of categories (level-0 only).
    $cats= array();
    $categories=get_categories($args);
    foreach($categories as $category) { 
        if ($category->parent == 0) {
            $cats[]= $category->term_id ;
        }       
    }
    $categories= implode(',',$cats);
    // Fetch the latest post of each category

    global $wpdb;

    $querystr = "SELECT wposts.* 
    FROM $wpdb->posts wposts
        LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
        LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    WHERE $wpdb->term_taxonomy.taxonomy = 'category'
        AND $wpdb->term_taxonomy.term_id IN($categories)
    ORDER BY wposts.post_date DESC";

    $pageposts = $wpdb->get_results($querystr, OBJECT);

And then the loop:

global $post;
        foreach ($pageposts as $k=>$post):
            setup_postdata($post);
            // ... wp loop through your results


}

Answer 1

Quantity of queries isn't the only measure of performance, an individual query can cost more than numerous simple queries.

The cost of (small query) x12 < cost of large query x1 + string/array manipulation

You would be better doing the 12 individual queries. If it is costly, store the result in a transient for a later time to save processing.

But even better, you could also update the values using a hook on save_post so that no 'search' is necessary, and it becomes a simple retrieve a stored value operation. This way if you store it and retrieve it via get_option, no extra queries will be executed as Wordpress has already loaded the value and will not make an unnecessary SQL query to retrieve it a second time.

// in functions.php
function check_category_latest($post_id){
    // check the categories this post is assigned to
    // foreach category this is assigned to
        // if it's the latest in that category
            set_option('mytheme_cat_'.$term_id,$post_id);
}
add_hook('save_post','check_category_latest');

// where you want to display your posts
$categories=get_categories($args);
foreach($categories as $category) {
    $post_id = get_option('mytheme_cat_'.$category->term_id);
    // do post display stuff
}

Something along those lines will give you what you want, and it'll do it for all the sub categories too, and it'll be much much cheaper than 12 individual queries, or even the 1 big expensive query