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

[Paul]

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