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

[Mike Schinkel]

While that approach (probably) does produce the desired output, it generates 2 additional queries per category PER post inspected (though I'm not sure that it works generally.) For it to work correctly in all cases you'd have to loop through every post in the DB and then run in_category() for each post which itself generates two SQL queries per call. Performance wise it's much, much worse than Nathan started with.  Try running it on a site with 10,000 posts and you'll see what I mean.

The thing is, ignoring wp-super-cache, you just can't get around doing a proper SQL query if you want reasonable performance.

-Mike Schinkel
Custom Wordpress Plugins
http://mikeschinkel.com/custom-wordpress-plugins

----- Original Message -----
From: "Glenn Ansley" <glenn at glennansley.com>
To: wp-hackers at lists.automattic.com
Sent: Tuesday, May 5, 2009 2:27:48 AM GMT -05:00 US/Canada Eastern
Subject: Re: [wp-hackers] Pull Latest Post from Each of 6 Categories

I'm working with the loop found in index.php of the kubrick (default) theme.

1) Place the following code directly above <?php if (have_posts()) : ?>

<?php $featured_cats = array(1,2,3,4,5,6); ?>

2) Place the following code directly below <?php if (have_posts()) :
?> and above <?php while (have_posts()) : the_post(); ?>

<?php while (have_posts()) : the_post(); ?>
    <?php
        foreach ( $featured_cats as $key => $cat ){
            if ( in_category( $cat , $post->ID ) && !isset($features[$cat]) ){
                $features[$cat] = $post;
            }
        }
    ?>
<?php endwhile; ?>

<?php if( $features[1] ) { echo "<h2>".$features[1]->post_title."</h2>"; } ?>

<?php if( $features[2] ) { echo "<h2>".$features[2]->post_title."</h2>"; } ?>

3) There is no step three.

All I did was run the while loop through one more time than normal,
populating an array with the first post found for each of your
featured categories.
Once the loop runs through once, you have your array and you can spit
out the data wherever you want.

If normal WP Loop logic is 1) Query DB 2) Do the loop and spit out the posts,
My altered one says 1) Query the DB 2) Do the loop and grab your
featured posts. 3) Do the loop again and spit out your normal posts.
Again, a rewind_posts() might be in order at step 2.5.

Hope that helps (or at least points in you the right direction).

Glenn Ansley
http://fullthrottledevelopment.com

Your default loop should still run fine. I didn't have to on my local
install, but there is a possibility that you'll have to rewind the
loop if it isn't working correctly.

On Tue, May 5, 2009 at 1:13 AM, Paul <paul at codehooligans.com> wrote:
> One final comment on this in defense of my SQL query before heading to bed.
>
> Austin, You are correct to point out the various issues with the SQL code I
> provided as an example. But these sections were gathered from the exact SQL
> code used by WP_Query itself when viewing an archive for a given single
> category on my own laptop. Try it yourself. In the head of your template
> before the loop code add:
>
>        echo "wp_query<pre>"; print_r($wp_query); echo "</pre>";
>
>
> You will the same basic SQL code with the 'WHERE 1=1' and the "AND
> (wp_posts.post_status =  'publish' OR wp_posts.post_status =  'private')"
> statements. These private posts are automatically filtered out when using
> the WP_Query loop code based on the user status. This is why I chose to
> return the WP_Query reference as a 'default'.
>
> I'm out.
>
> P-
>
>
> On May 4, 2009, at 9:42 PM, Austin Matzko wrote:
>
>> 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.
>> _______________________________________________
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
_______________________________________________
wp-hackers mailing list
wp-hackers at lists.automattic.com
http://lists.automattic.com/mailman/listinfo/wp-hackers