Custom $wpdb Query for Custom Post Type by Category

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)." rel="nofollow noreferrer noopener"?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="#" rel="nofollow noreferrer noopener" rel="nofollow noreferrer noopener"><?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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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="nofollow noreferrer noopener" rel="nofollow noreferrer noopener" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>

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

Method 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()" rel="nofollow noreferrer noopener"><?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); ?>" rel="nofollow noreferrer noopener">
            <?php echo date( 'F', mktime(0, 0, 0, $month) );?></a>
        </li>
    <?php endforeach;?>
    </ul>
    </li>
    <?php endforeach; ?>

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0