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

[Mike Schinkel]

While I am often the critique of premature optimization I think Nathan is probably warranted in using SQL in this case.

"Austin Matzko" <if.website at gmail.com> wrote:
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));

How is that better?  It's 14 SQL queries instead of Nathan's 12 or instead of Paul's 2 (2x queries because SQL_CALC_FOUND_ROWS is called first for each query.)

> This has the advantage of using the WordPress API, so you can 
> remain agnostic about future database schema changes 

Sometimes the medicine can be worse than the ailment, no? If his site is too slow it's better to use direct SQL to resolve the issues.

> and take advantage of the built-in caching. 

Can you clarify this?  I might be missing something.

> 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 

How is that really a problem?

> (including the "WHERE 1 =1" that serves 
> no purpose here).  

Agreed but I don't think Paul intended to present optimized code, instead just a proof of concept.

> 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.

Which is only an issue if not just for Nathan's site. 

Besides, if for a plugin many plugins require v5.0, what's really wrong with a plugin requiring v4.1? He could code in a slow way for MySQL v4.0 and do it the fast way for v4.1; version issue solved.

> 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.

Can you clarify this with examples?  I might be missing something.
 
> * It's vulnerable to future database changes.

Nathan can write code to anticipate this and fall back in case of an issue.

>  * It's more likely to contain inadvertent missteps.  For 
> example, see this line:

I don't see this as valid criticism; you can just as easily call get_posts() incorrectly; both the WPDB scheme and the get_posts() calling interface are filled with idiosyncrasies.


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