[wp-hackers] Pull Latest Post from Each of 6 Categories

[Austin Matzko]

On Mon, May 4, 2009 at 4:42 PM, Nathan Rice <ncrice at gmail.com> wrote:
> I have a site which has a section on the homepage that pulls the latest post
> from each of 6 different categories.  As of now, I'm running 6 separate
> instances of WP_Query to get the latest post from each of the 6 categories.

On Mon, May 4, 2009 at 7:43 PM, Nathan Rice <ncrice at gmail.com> wrote:
> Thanks guys ... I'm basically hearing that there's no way for WP_Query to do
> this without some sort of custom SQL query.

I would do something like this, where 1-6 are the category ids:

$post_ids = array(0);
foreach( array(1, 2, 3, 4, 5, 6) as $cat_id ) {
        if ( $posts = get_posts(array('cat' => $cat_id, 'showposts' => 1)) ) {
                $first = array_shift($posts);
                $post_ids[] = $first->ID;
        }
}
query_posts(array('post__in' => $post_ids));

This has the advantage of using the WordPress API, so you can remain
agnostic about future database schema changes and take advantage of
the built-in caching. You can also use the regular Loop following this
code.


On Mon, May 4, 2009 at 8:09 PM, Paul <paul at codehooligans.com> wrote:
> The SQL involved is not really that complicated but does require subqueries,
> one for each category. So you need to make sure your MySQL is up to 4.1 (or
> better 5.x). Below is the SQL for the three categories 9,157,8. As you can
> see it's alot but repeats the same basic SQL for each category
>
> SELECT * FROM wp_posts WHERE ID = (
>                SELECT wp_posts.ID
>                FROM wp_posts
>                        INNER JOIN wp_term_relationships ON ( wp_posts.ID =
> wp_term_relationships.object_id )
>                        INNER JOIN wp_term_taxonomy ON (
> wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id )
>                WHERE 1 =1
>                        AND wp_term_taxonomy.taxonomy =  'category'
>                        AND wp_term_taxonomy.term_id IN ('9')
>                        AND wp_posts.post_type =  'post'
>                        AND (wp_posts.post_status =  'publish' OR
> wp_posts.post_status =  'private')
>                ORDER BY wp_posts.post_date DESC
>                LIMIT 1

I would recommend against approaches like this one.  For one thing,
this subquery is more or less the same query as done by the main
WordPress query that would run if you used the WordPress API instead
(including the "WHERE 1 =1" that serves no purpose here).  So there's
no MySQL-level advantage, and there's a slight disadvantage (as Paul
points out) in that subqueries aren't supported by  MySQL 4.0,
WordPress's minimum required version.

But unlike using the WordPress API, a direct MySQL query like this has
several disadvantages.
 * It might not be cached in a manner consistent with the rest of WordPress.
 * It's vulnerable to future database changes.
 * It's more likely to contain inadvertent missteps.  For example, see
this line:

> AND (wp_posts.post_status =  'publish' OR wp_posts.post_status =  'private')

Whoops!  Now we could be displaying private posts in that category.