[wp-hackers] Pull Latest Post from Each of 6 Categories
Paul
paul at codehooligans.com
Tue May 5 03:26:45 GMT 2009
On May 4, 2009, at 9:15 PM, Nathan Rice wrote:
> 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.
Not stupid at all. In my opinion (and surely mine alone) its better to
use the single query with sub-queries as I've demonstrated over using
PHP (and WordPress) to manage multiple queries. Every time you create
an instance of the WP_Query object you will be using memory. The
question is from a performance stand point does the multiple WP_Query
runs provide less memory use than the one SQL query. Answer, hard to
advise. There are so many variables like how well MySQL is tuned. How
much memory is on the server to prevent paging, etc. I'm sure there
are many other more knowledgeable users on this list.
The example I provided was really just a test for myself to see if I
could put together the actual query in a single SQL statement. As
others have mentioned this is particularly trouble prone since you are
using direct SQL which is severely frowned upon as it has the
potential to break if the schema is ever changed as we for WP 2.3.
>
> 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.
>
Agreed.
> At the very least, this points out a limitation in the WP_Query object
> parameters.
I'm not sure this is a correct statement. Sure, WP_Query does not
provide the flexibly needed for this specific needs. I do think
WP_Query is somewhat state of the art in it's design. It's just that
the author(s) did not anticipate your needs when creating it. If you
think about how WP is mostly used it's more for getting a list of
items based on time sorting. In your case you want only one post from
each category which break its model.
>
> 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