Custom $wpdb Query for Custom Post Type by Category

Question

I am using the following code to output a list of items in my 'documents' Custom Post type by Year and Month.

<ul>
<?php
$post_type = 'documents';
global $wpdb;
/**/
$years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' ORDER BY post_date DESC");
foreach($years as $year) :
$years_count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' AND YEAR(post_date) = ".$year.";" ) );
?>
<li class="year"><?php echo $year; ?> (<?php echo $years_count; ?> Posts)
    <ul>
    <? $months = $wpdb->get_col("SELECT DISTINCT MONTH(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' AND YEAR(post_date) = '".$year."' ORDER BY post_date DESC");
    foreach($months as $month) :
    $months_count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' AND YEAR(post_date) = ".$year." and MONTH(post_date) = ".$month.";" ) );
    ?>
        <li class="month">>
        <a href="<?php echo get_month_link($year, $month)."?post_type=".$post_type; ?>"><?php echo date( 'F', mktime(0, 0, 0, $month) );?></a> (<?php echo $months_count; ?> Posts)

        <?php  $theids = $wpdb->get_results("SELECT ID, post_title, post_date FROM $wpdb->posts WHERE post_type = '".$post_type."' AND post_status = 'publish' AND YEAR(post_date) = ".$year." and MONTH(post_date) = ".$month.";");
                foreach ($theids as $theid):    

                    ?>
                    <h4 style="font-style:italic;"><li><a href="#"><?php echo $theid->post_title; ?></a></li></h4>                            

                    <?php
                    endforeach; ?>

        </li>
    <?php endforeach;?>
    </ul>
</li>
<?php endforeach; ?>
</ul>

Currently this is working as expected but I am having problems trying to do the same thing for a specific category within my documents post type. How would I refine this code for my category 'cascade-campus-docs' within my 'documents' post type.

Answer 1

Thanks SickHippie.

This is what I ended up with:

<?php $args = array(
        'posts_per_page' => -1,
        'post_type' => 'documents',
        'post_status' => 'publish',
        'tax_query' => array(
            array(
                'taxonomy' => 'download-category',
                'field' => 'slug',
                'terms' => 'cascade-campus-docs'
            )
        )
    );   
    $my_query = null;
    $my_query = new WP_Query($args); 
?>

<?php if( $my_query->have_posts() ):
        while ($my_query->have_posts()) : $my_query->the_post(); ?>

<?php
    $year = get_the_date('Y', '', '', FALSE);

     if ($year !== $year_check) {
     echo "<h2 class='year'>" . $year . "</h2>";
     }
     $year_check = $year;
?>

<?php the_date('F', '<p class="the_date"><span>', '</span></p>'); ?>
   <p><a href="#" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>

<?php endwhile;
    endif;
wp_reset_query();  ?>

Answer 2

Don't use $wpdb, use wp_query. Not only will your code be leaner and quicker, you'll avoid issues like this. Unless you're doing something with custom tables or seriously in-depth SQL, there's no reason to do direct SQL calls on $wpdb.

So, to answer your question - rewrite it from the ground up using wp_query, see that everything you want to do is documented in the Codex, solve your problems without any hassle, and pat yourself on the back, a job well done.

Answer 3

I was searching for this for the last one week but found nothing. Hopefully got this query to display specific category posts only:

<?php
    $years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id AND $wpdb->term_taxonomy.taxonomy='category') INNER JOIN $wpdb->terms ON ($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id) WHERE $wpdb->terms.slug='blog' ORDER BY post_date DESC");
    foreach($years as $year) : 
    ?>
<li><a href="JavaScript:void()"><?php echo $year; ?></a>        
    <ul class="archive-sub-menu">
    <?  $months = $wpdb->get_col("SELECT DISTINCT MONTH(post_date)
    FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'post'
    AND YEAR(post_date) = '".$year."' ORDER BY post_date DESC");
    foreach($months as $month) :
    ?>
<li><a href="<?php echo get_month_link($year, $month); ?>">
            <?php echo date( 'F', mktime(0, 0, 0, $month) );?></a>
        </li>
    <?php endforeach;?>
    </ul>
    </li>
    <?php endforeach; ?>