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

[Nathan Rice]

Paul,
Would this be more, or less, expensive of a process than the 6 separate
WP_Query objects? That might sound like a stupid question, but I'm a
complete noob when it comes to SQL processes.

This particular site has had trouble in the past with database overload
(before wp_super_cache), so I want to be sure that I'm using the least
taxing method available.

Then again, if wp-super-cache is taking care of reducing queries, then this
may all be unnecessary.  I just need to make sure I'm doing everything I can
do to make it better.

At the very least, this points out a limitation in the WP_Query object
parameters.

My Website
http://www.nathanrice.net/

My Twitter
http://twitter.com/nathanrice


On Mon, May 4, 2009 at 9:09 PM, Paul <paul at codehooligans.com> wrote:

> Yeah. Not *that* complicated.
>
> Nathan if you are still looking for something I have something for you. I
> can't figure out how to do this via WP_Query directly. I've not really used
> WP_Query that much for custom query only for filtering categories, tags,
> etc. I found it easier to build the query then call  wpdb->get_results()
> function directly. This will return the result set (array). From there I can
> 'fake' the WP_Query object.
>
> I'm hesitant to just paste the code here. So I've added the file to my site
> for download as a text file.
> http://www.codehooligans.com/packages/page_test.txt
>
> There is a leadin function for you to use. You can paste the function
> itself into your theme's functions.php file.
>
>
> The lead-in function:
>
> get_single_cat_items();
>
> Take a number of arguments in traditional WP style:
>
> get_single_cat_items('cats=9,157,8&return=wpquery&cache-key=single_cats');
>
> The above will query the posts for categories 9, 157 and 8.
> The 'return' parameter is used to tell the function to return a reference
> to a WP_Query object (default) or the raw posts array.
> The 'cache-key' if set will use the old wp_cache_get(); function to prevent
> requery.
>
> 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
>        )
>        OR 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 ('157')
>                        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
>        )
>        OR 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 ('8')
>                        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
>        )
>
>
>
>
>
> On May 4, 2009, at 6:05 PM, Jeremy Clarke wrote:
>
>  Yeah, the SQL to accomplish this would be so complicated and prone to
>> breaking that you're probably better off working out an
>> intricately-complex caching system and using normal queries instead.
>>
>> Also: If its just the front page of your site then as long as you have
>> wp-supercache or something like that running you probably won't be hit
>> too hard because that page will almost always be cached for visitors.
>>
>> --
>> Jeremy Clarke
>> Code and Design | globalvoicesonline.org
>> _______________________________________________
>> 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
>