I need to write a function to get all the categories for a given year. Since the creation date of a taxonomy isn't stored in the database, I'm not really sure what to do. My initial thought was to get all posts for a given year and somehow find all categories associated with those posts, but I'm not sure how to do that and it sounds horrifically slow. Perhaps I could just write some SQL but I'm much more well versed in PHP. So again back to the function, I imagine it would like something like:

function get_categories_by_year($year) {
 // Do stuff...
 return $array_of_categories_in_year;

This is actually for a custom taxonomy but since categories are the default hierarchical taxonomy I decided to use it as an example. I'm happy to provide more info on what the output is supposed to look like if it's relevant. Thank's for the help!


I would do this with SQL query. Doing it in PHP will be a little bit slow, I guess.

This function should do the job:

function get_categories_from_year($year) {
    $sql = "select term.* 
        from {$wpdb->terms} term
            inner join {$wpdb->term_taxonomy} tax on (term.term_id = tax.term_id)
            inner join {$wpdb->term_relationships} relation on (tax.term_taxonomy_id = relation.term_taxonomy_id)
            inner join {$wpdb->posts} post on (relation.object_id = post.ID)
        where tax.taxonomy = 'category' and YEAR(post.post_date) = %d";
    return $wpdb->get_results( $wpdb->prepare($sql, $year) );
| improve this answer | |
  • Sounds about right. I would expand this and store the result in a transient so it's cached and you don't have to run the SQL query over and over again on each page load. – kingkool68 Aug 4 '13 at 14:56
  • Thanks for the answer this seems to have done the trick. I wasn't able to get around to checking it until now, but looks good. Thanks! – ian Aug 27 '13 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.