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

[Paul]

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