How to reduce the number of queries?

Question

Try this:

global $wpdb; 
$num_cat = 6;
$query = "SELECT tt.term_taxonomy_id, t.name, COUNT( t.name ),
          GROUP_CONCAT( p.ID ) , GROUP_CONCAT(m.meta_value)
          FROM  {$wpdb->prefix}term_taxonomy tt
          NATURAL JOIN {$wpdb->prefix}terms t
          NATURAL JOIN {$wpdb->prefix}term_relationships tr
          JOIN {$wpdb->prefix}posts p ON ( tr.object_id = ID ) 
          JOIN {$wpdb->prefix}postmeta m ON ( post_id = ID ) 
          WHERE taxonomy = 'category'
          AND post_type="post"
          AND post_status="publish"
          AND meta_key = 'Due'
          AND meta_value > ".date('Ymd').
          "GROUP BY name
           ORDER BY COUNT( name ) DESC LIMIT $num_cat";
 $top_cats = $wpdb->get_results($query);

 foreach ($top_cats as $key => $cat) {
     $thumbnail_id = get_option('seamless_term_thumb_' . $cat->term_taxonomy_id);
     $image = wp_get_attachment_url($thumbnail_id);
    /* ...etc... */
 }

Intuitively, the query “first” gets the category ids and their names from wp_terms_taxonomy and wp_terms, “then” uses wp_term_relationships to get the posts in each category, “then” filters the posts by due date using wp_postmeta. “Finally,” the results are grouped and counted by category, and sorted by count. (I use quotes because the db manager can perform the query anyway it wants, not necessarily in this order).

For convenience and easy verification, the post ids in each category, and their corresponding due dates, are concatenated in a single string using mysql’s GROUP_CONCAT. Just try it! Then you can remove it from the code.

Note that you should use $cat->term_taxonomy_id rather than $cat->term_id (that applies to your code too), since a term_id can be associated to more than one taxonomy (e.g. a term with identical slug in different taxonomies). This can lead to very obscure bugs, precisely because it often goes unnoticed. The term_taxonomy_id represents the pair (term_id, taxonomy), which is guaranteed to be unique.

You can actually reduce the number of queries even more by adding the thumbnail_id to each row, modifying the query with an additional join. Something like this (untested):

SELECT ..., o.meta_value as thumbnail_id 
FROM ... JOIN {$wpdb->prefix}options o 
         ON (o.meta_key = CONCAT('seamless_term_thumb_', tt.term_taxonomy_id))

Presumably something similar can be done for the thumbnail’s url.

Related Posts: