count posts from custom taxonomy terms by year

Question

I want to build statistics-like tables with the count of posts in given custom taxonomy terms and display them by year along with the total post count from that year.

For example:

2014

|_Taxonomy Term A: 8 posts

|_ Taxonomy Term B: 12 posts

Total posts in 2014: 20 posts

The function obviously needs to do the following:

• count posts from my-custom-post-type that have been published in 2014, 2013, ... (total count per year)
• count the posts in each taxonomy term from my-custom-taxonomy on the yearly base

To make a dynamic list for the years, I used a snippet I found somewhere and it looks like this:

function posts_by_year() {
  // array to use for results
  $years = array();

  // get posts from WP
  $posts = get_posts(array(
    'numberposts' => -1,
    'orderby' => 'post_date',
    'order' => 'ASC',
    'post_type' => 'my-custom-post-type',
    'post_status' => 'publish'
  ));

  // loop through posts, populating $years arrays
  foreach($posts as $post) {
    $years[date('Y', strtotime($post->post_date))][] = $post;
  }

  // reverse sort by year
  krsort($years);
  return $years;
}

In my custom page template, I use:

<?php foreach(posts_by_year() as $year => $posts) : ?>
<h2><?php echo $year; ?></h2>
// the code that I need to display the post counts per year
<?php endforeach; ?>

My question is:

How do I build the wp_query to be able to output the post count per taxonomy term per year? I'd be so glad if anyone helped me sort that out.

PS: I already have a table counting ALL published posts from my-custom-post-type per taxonomy term, I found help here and used deflime's code.

Edit:

This is Pieter Goosen's snippet including my modifications:

$oldest = get_posts( 'post_type=my-custom-post-type&post_status=publish&posts_per_page=1&order=ASC' );
$oldest_date = $oldest[0]->post_date;

$first_date = date('Y', strtotime($oldest_date));
$todays_date = date('Y');

$year_range = range($todays_date, $first_date);

foreach ($year_range as $year) { // dynamic year-based tables
    echo '<h2>' . $year . '</h2>';
    $terms = get_terms('my-custom-taxonomy');
    $total_posts = 0;

    if ( !empty( $terms ) && !is_wp_error( $terms ) ) { // table body

        echo '
            <table class="statistics">
            <tbody>
            ';
        echo '
            <thead>
                <tr>
                    <td>Taxonomy Term</td>
                    <td>Percentage</td>
                    <td class="chart-count">Count</td>
                </tr>
            </thead>
            ';
        echo '
            <tfoot>
                <tr>
                <td colspan="2">Posts total</td>
                <td class="chart-count">'.$total_posts.'</td>
                </tr>
            </tfoot>
            ';

        foreach ( $terms as $term ) { // setup table <tr> per taxonomy term
            $args = array(
                'posts_per_page'    => -1,
                'post_type'         => 'my-custom-post-type',
                'post_status'       => 'publish',
                'year'              => $year,
                'tax_query' => array(
                    array(
                        'taxonomy' => 'my-custom-taxonomy',
                        'field'    => 'slug',
                        'terms'    => $term->slug
                    ),
                ),
            );

            $total_posts += $term->count;
            // Get  %, round to 2 decimal places
            $percentage = round( (($yearly_posts_per_term->post_count / $total_posts)*100), 2 );
            // will add up to 100 at the end?
            $total_check += $percentage;

            $yearly_posts_per_term = new WP_Query($args);

            echo '
                <tr>
                    <td class="chart-item">'.$term->name.'</td>
                    <td class="chart-visual"><div class="chart-bar" style="width:'.$percentage.'%;"></div> '.$percentage.'%</td>
                    <td class="chart-count">'.$yearly_posts_per_term->post_count.'</td>
                </tr>
            ';

        } // endforeach
        echo '
            </tbody>
            </table>
            '; 
    } //end of table
} // end of year-based list

Answer 1

EDIT 2

Here is another version of the code in EDIT 1. This code is about much more faster. Here is my test between the code in EDIT 1 and EDIT 2

• EDIT 1 Database query time = +/- 0.25 and Database queries = 69

• EDIT 2 Database query time = +/- 0.07 and Database queries = 29

Here is the code

<?php

 $oldest = get_posts( 'post_type=post&post_status=publish&posts_per_page=1&order=ASC' );
    $oldest_date = $oldest[0]->post_date;

    $first_date = date('Y', strtotime($oldest_date));
    $todays_date = date('Y');

    $year_range = range($todays_date, $first_date);

    foreach ($year_range as $year) { // dynamic year-based tables
        echo '<h2>' . $year . '</h2>';
        $terms = get_terms('category');

        $term_slugs = array();

        if ( !empty( $terms ) && !is_wp_error( $terms ) ) { // table body

            foreach ( $terms as $key=>$term){
                $term_slugs[$key] = $term->slug;
            }

            echo '
                <table class="statistics">
                <tbody>
                ';
            echo '
                <thead>
                    <tr>
                        <td>Taxonomy Term</td>
                        <td>Percentage</td>
                        <td class="chart-count">Count</td>
                    </tr>
                </thead>
                ';

            $posts_count = array(); // Holds all term post counts in an array
            $terms_array = array();  // Holds all term names in an array 

                $args = array(
                    'posts_per_page'    => -1,
                    'post_type'         => 'post',
                    'post_status'       => 'publish',
                    'year'              => $year,
                    'tax_query' => array(
                        array(
                            'taxonomy'          => 'category',
                            'field'             => 'slug',
                            'terms'             => $term_slugs,
                            'include_children'  => false 
                        ),
                    ),
                );

                $yearly_posts_per_term = new WP_Query($args);
                    $posts_count[] = $yearly_posts_per_term->post_count; //Collects post counts and send them to an array

                if($yearly_posts_per_term->have_posts()):
                    while($yearly_posts_per_term->have_posts()): $yearly_posts_per_term->the_post();

                        $terms = get_the_terms( $post->ID, 'category' );

                        if ( $terms && ! is_wp_error( $terms ) ) {
                            foreach ( $terms as $term ) {
                                $terms_array[] = $term->slug;
                            }
                        } 

                    endwhile;
                endif;

        }

        $total_posts = array_sum($posts_count); //Use array_sum to add up all the separate post counts

        $result = array_count_values($terms_array);

        foreach ($result as $term_name=>$count) {

            $percentage = round( (($count / $total_posts)*100), 2 ); //Calculate the percentages of each term post cound to total year post count

            echo '
                    <tr>
                        <td class="chart-item">'.$term_name.'</td>
                        <td class="chart-visual"><div class="chart-bar" style="width:'.$percentage.'%;"></div> '.$percentage.'%</td>
                        <td class="chart-count">'.$count.'</td>
                    </tr>
                ';
        }   

            echo '
                <tfoot>
                    <tr>
                    <td colspan="2">Posts total</td>
                    <td class="chart-count">'.$total_posts.'</td>
                    </tr>
                </tfoot>
                ';

            echo '
                </tbody>
                </table>
                '; 
    } // end of year-based list

?>

This renders the same output as the table in EDIT 1, except, it does not show empty terms, only terms with posts are shown

EDIT 1

From your edited question, here is the strip down of the new code. I had to scrap one or two things here and rearrange some elements to make this work. The big challenge here was to calculate the percentages, because the variables that is used to calculate this lived in separate foreach loops. Variables within foreach loops only lives within that foreach, not outsie

The big changes to the code (from my original answer, @deflime code and your integrated code) from your edit are

• Moved the two tables holding the total posts count and the percentages and term names to outside just below the $terms foreach loop

• Pushed the term names and posts count from each term to an array outside the $terms foreach loop

• Scrapped @deflime code, removed $total_posts = 0; and only kept and modified $percentage = round( (($yearly_posts_per_term->post_count / $total_posts)*100), 2 );

• Used array_sum to get the total amount of posts for the year from the array of post counts per term

• Used array_combine to create an associative array with the term names and post counts from each term

• Finally, I used a foreach loop to get each term name and the associated post count, to feed that back into the table

Here is the final code

<?php

 $oldest = get_posts( 'post_type=post&post_status=publish&posts_per_page=1&order=ASC' );
    $oldest_date = $oldest[0]->post_date;

    $first_date = date('Y', strtotime($oldest_date));
    $todays_date = date('Y');

    $year_range = range($todays_date, $first_date);

    foreach ($year_range as $year) { // dynamic year-based tables
        echo '<h2>' . $year . '</h2>';
        $terms = get_terms('category');

        if ( !empty( $terms ) && !is_wp_error( $terms ) ) { // table body

            echo '
                <table class="statistics">
                <tbody>
                ';
            echo '
                <thead>
                    <tr>
                        <td>Taxonomy Term</td>
                        <td>Percentage</td>
                        <td class="chart-count">Count</td>
                    </tr>
                </thead>
                ';

            $posts_count = array(); // Holds all term post counts in an array
            $term_names = array();  // Holds all term names in an array

            foreach($terms as $term) {
                $term_names[] = $term->name; //Collects term names and send them to an array

                $args = array(
                    'posts_per_page'    => -1,
                    'post_type'         => 'post',
                    'post_status'       => 'publish',
                    'year'              => $year,
                    'tax_query' => array(
                        array(
                            'taxonomy'          => 'category',
                            'field'             => 'slug',
                            'terms'             => $term->slug,
                            'include_children'  => false 
                        ),
                    ),
                );

                $yearly_posts_per_term = new WP_Query($args);
                    $posts_count[] = $yearly_posts_per_term->post_count; //Collects post counts and send them to an array

            } // endforeach

            unset($term);

        }

        $total_posts = array_sum($posts_count); //Use array_sum to add up all the separate post counts

        $combine = array_combine($term_names,$posts_count); //Use array_combine to combine term names and post counts into assosiative array

        foreach ($combine as $term_name=>$count) {

            $percentage = round( (($count / $total_posts)*100), 2 ); //Calculate the percentages of each term post cound to total year post count

            echo '
                    <tr>
                        <td class="chart-item">'.$term_name.'</td>
                        <td class="chart-visual"><div class="chart-bar" style="width:'.$percentage.'%;"></div> '.$percentage.'%</td>
                        <td class="chart-count">'.$count.'</td>
                    </tr>
                ';
        }   

            echo '
                <tfoot>
                    <tr>
                    <td colspan="2">Posts total</td>
                    <td class="chart-count">'.$total_posts.'</td>
                    </tr>
                </tfoot>
                ';

            echo '
                </tbody>
                </table>
                '; 
    } // end of year-based list

?>

Please note As in my original answer, I've changed the post type to post and taxonomy to category for the purpose of testing.

Your end result is a table looking like this. Please note All my term names are in Afrikaans as I tested it on my test site that is in Afrikaans.

ORIGINAL ANSWER

This is a very rough draft of an idea I had on how to do this. I've included no HTML mark-up and used the default post type post and the build-in taxonomy category to test the code.

Here is how I constructed the complete query

• First, get the date of the oldest post (should be the first post) on the site. This is done by a simple get_posts query. Modify to suite your needs

  $oldest = get_posts( 'post_status=publish&posts_per_page=1&order=ASC' );
      $oldest_date = $oldest[0]->post_date;
  

• Next, strip the returned date to only get the year from the post date. Use strtotime() function to convert the year into a Unix timestamp

  $first_date = date('Y', strtotime($oldest_date));
  

• Return the current date, you would want only the year. Use the date() function

  $current_date = date('Y');
  

• Return both dates to the range() function to print a range of years between the two dates

  $year_range = range($current_date, $first_date);
  

• Add these ranges back into a foreach loop to get your posts in year based lists

• I've used get_terms() to geta list of all the available terms of the taxonomy in question

  $terms = get_terms('category');
  

• Now, all of this info needs to be fed back into a tax_query using WP_Query

  $args = array(
          'posts_per_page'    => -1,
          'post_type'         => 'post',
          'post_status'       => 'publish',
          'year'              => $year,
          'tax_query' => array(
              array(
                  'taxonomy' => 'category',
                  'field'    => 'slug',
                  'terms'    => $term->slug
              ),
          ),
      );
  
  $posts = new WP_Query($args);
  

• Lastly, you want to return the term name and the amount of posts per term

  echo $term->name . '(' . $posts->post_count . ')';
  

Now all together!!

<?php
$oldest = get_posts( 'post_status=publish&posts_per_page=1&order=ASC' );
    $oldest_date = $oldest[0]->post_date;

$first_date = date('Y', strtotime($oldest_date));
$current_date = date('Y');

$year_range = range($current_date, $first_date);

foreach ($year_range as $year) {
    echo $year;

    $terms = get_terms('category');
    if ( !empty( $terms ) && !is_wp_error( $terms ) ){

        foreach ( $terms as $term ) {
            $args = array(
                'posts_per_page'    => -1,
                'post_type'         => 'post',
                'post_status'       => 'publish',
                'year'              => $year,
                'tax_query' => array(
                    array(
                        'taxonomy' => 'category',
                        'field'    => 'slug',
                        'terms'    => $term->slug
                    ),
                ),
            );

            $posts = new WP_Query($args);

                echo $term->name . '(' . $posts->post_count . ')';
        }
    }
}
 ?>

As said, this can be refined, so take this idea and code and adapt and modify as you see fit. Hope this helps.

Answer 2

There is a much simpler solution than the one you accepted, using just one single query. I illustrate here for the custom post type 'product' and the custom taxonomy 'product_cat'(egory) in Woocommerce, just because I happen to have a handy install to test it. The $query is:

SELECT YEAR(p.post_date), t.name, COUNT(*), GROUP_CONCAT(p.ID), GROUP_CONCAT(p.post_title)
FROM wp_posts p
JOIN wp_term_relationships tr ON p.ID = tr.object_id
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE tt.taxonomy = 'product_cat' AND p.post_type = 'product' AND p.post_status='publish'
GROUP BY YEAR(p.post_date), tt.term_taxonomy_id
ORDER by YEAR(p.post_date) DESC, tt.term_taxonomy_id ASC

In my sample install, this yields:

So for example there are 10 posts of Clothing in 2013 and 2 in 2012.

You just need to call $wpdb->get_results($query) (and use $wpdb->prefix instead of 'wp_') to get this table into an array or object, calculate the percentages, and display them. The group_concat columns are added mostly for debugging (thus you probably want to remove them), though on the other hand the id's, for example, may also come handy for other processing (by exploding values in the column into arrays).

Answer 3

In case someone is looking for a more easier shorter code to display only specific year instead of running a loop to display all the years from the post. This is the code .

This also includes link to the taxonomy term archive. `

    $terms = get_terms('your-taxonomy'); //grab the taxonomy name
    $year  = 2015; // The year you want to pull the terms and count from

    if ( !empty( $terms ) && !is_wp_error( $terms ) ){ 
    echo '<div class="barometer">'; //class to apply css if you want
        echo '<ul>'; 

            foreach ( $terms as $term ) { 
                $args = array(
            //'posts_per_page'    => -1,
            'post_type'         => 'post', // disable this line if you want to grap from all post types
            'post_status'       => 'publish',
            'year'              => $year,
            'tax_query' => array(
                array(
                    'taxonomy' => 'your-taxonomy',
                    'field'    => 'slug',
                    'terms'    => $term->slug
                    ),
                ),
            );

            $post_year = new WP_Query($args); 

                $term = sanitize_term( $term, 'your-taxonomy' ); 
                $term_link = get_term_link( $term, 'your-taxonomy' ); //Get the links to the term archive page

        // If the term has no post, it does not display. You can remove the if statement from here if you want to display empty terms   
        if ($post_year->post_count > 0 ) {

            echo '<li><a href="' . esc_url( $term_link ) .'" title="' . sprintf( __( 'View all %s stories','media-foundation' ), $term->name ) . '">' . $term->name . '<span>' .$post_year->post_count. '</span>' . '</a></li>'; 

        } // End of if $post_year->post_count

    } // End of Foreach term loop
        echo '</ul>';
    echo '</div>';          
} 

`