How to reduce the number of queries?

Question

I have meta key "Due" for posts....if a post has expired (past the current date or not equal to it) then it will not be displayed in the count. This is my code, which works perfectly. My only problem is that it creates over 100 queries. Is there any way to reduce this number? I am not sure how to optimize it....

/**
 * Function to list all category with thumbnail custom link, etc..
 *
 * How to use this function:
 * Add in template: <?php my_category_list(); ?>
 *
 */
function my_category_list(){

/* LIST OF CATS DATA */
$cats_data = array();

/**
 * Get Categories
 * @link http://codex.wordpress.org/Function_Reference/get_categories
 */
$cat_args = array(
    'hide_empty'    => 0,
    'exclude'       => '13,1,1460'
);
$categories = get_categories( $cat_args );

/* If category found, load list of category */
if ( !empty( $categories ) ) {
    $i = 0;

    /* Foreach category: display the data */
    foreach ( $categories as $cat) {

        /* ======= HTML CLASS ========= */
        /* dynamic class (?) need fix */
        $class = ( $i % 3 ) ? 'span4' : 'span4';
        $classes = $class . ' ' . 'category-' . $cat->term_id;

        /* ======= POST COUNT ========= */
        /* Get all posts in category + in due date
         * this only to get the post count.
         * @link http://themehybrid.com/support/topic/issue-with-filtering-due-meta-key
         */
        $query_args = array(
            'post_type'       => 'post',
            'category_name'   => $cat->slug,
            'meta_query' => array(
                array(
                    'key'        => 'Due',
                    'value'      => date( 'Ymd' ),
                    'type'       => 'DATE',
                    'compare'    => '>=', // greater than or equal to
                )
            )
        );
        $my_query = new WP_Query( $query_args );
        $post_count = $my_query->found_posts;

        /* ====== CATEGORY THUMBNAIL ======== */
        $thumbnail_id = get_option('seamless_term_thumb_' . $cat->term_id);
        $image = wp_get_attachment_url($thumbnail_id);

        /* ====== LINK TO SEARCH: no need fields ======= */
        $link_to = 'http://www.scholarships360.org/discover/?search_query=&orderby=blank&tax_category=' . $cat->slug .'&wpas=1';

        /* MERGE DATA IN ARRAY */
        $cats_data[] = array(
            'classes'      => $classes,
            'post_count'   => $post_count,
            'image'        => $image,
            'name'         => $cat->name,
            'link'         => $link_to,
        );

        $i++;
    } // end foreach

    /**
     * NOW THE FUN PART
     * =================
     */

    /* Sort Cat Data by Post Count */
    usort($cats_data, 'my_sort_cat_data');

    /* Cut only 6 item to display */
    $cats_data = array_slice( $cats_data, 0, 6 );

    /* Display it */
    foreach ($cats_data as $cat_data ){ ?>

        <div class="<?php echo $cat_data['classes'];?>">
            <div class="thumb one">
                <a href="<?php echo $cat_data['link'] ?>">
                    <div class="two"><?php echo  $cat_data['post_count'] . ' Scholarships' ?></div>
                </a>
                <a href="<?php echo $cat_data['link'] ?>">
                    <img src="<?php echo $cat_data['image']; ?>" alt="<?php echo esc_attr( $cat_data['name'] ); ?>" class="item-image">
                </a>
            </div>  <!-- end .thumb -->
        </div>
    <?php 
    }
}
/* No category found */
else {
    echo '<p>No category found...</p>';
}
}

 /**
 * Sort Cat Data Helper Function
 * @link http://stackoverflow.com/questions/2699086/sort-multidimensional-array-by-value-2
 */
function my_sort_cat_data( $a, $b ){
 return $b['post_count'] - $a['post_count'];
}

Answer 1

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.