[wp-hackers] Pull Latest Post from Each of 6 Categories
Mike Schinkel
mikeschinkel at newclarity.net
Tue May 5 08:09:01 GMT 2009
Nathan:
Sorry, I think I was having a brain fart. I think this is the SQL you would need, assuming that you can depend on larger post IDs being the more frequent route, and assuming you'd go the SQL route:
SELECT wp_terms.term_id, wp_terms.name AS term_name, wp_posts.*
FROM wp_posts
INNER JOIN (
SELECT wp_term_taxonomy.term_id, MAX(wp_posts.ID) AS post_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 wp_term_taxonomy.taxonomy = 'category' AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
GROUP BY wp_term_taxonomy.term_id) AS post_category ON post_category.post_id=wp_posts.ID
INNER JOIN wp_terms ON wp_terms.term_id=post_category.term_id
HTH.
-Mike Schinkel
Custom Wordpress Plugins
http://mikeschinkel.com/custom-wordpress-plugins
----- Original Message -----
From: "Nathan Rice" <ncrice at gmail.com>
To: wp-hackers at lists.automattic.com
Sent: Monday, May 4, 2009 10:15:13 PM GMT -05:00 US/Canada Eastern
Subject: Re: [wp-hackers] Pull Latest Post from Each of 6 Categories
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
>
_______________________________________________
wp-hackers mailing list
wp-hackers at lists.automattic.com
http://lists.automattic.com/mailman/listinfo/wp-hackers
More information about the wp-hackers
mailing list