How to determine which years there are posts for in a category?

  • Hi,

    How can I determine which years there are post for in a particular category? I’ve stumbled across the following database query in the codex, but I can’t figure out how to add a category condition (given the new terms tables and all).

    $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_date DESC");

    Can anyone suggest a solution? Perhaps there is a way to do this without the custom database query?

    Thanks!

Viewing 1 replies (of 1 total)
  • Thread Starter triffid

    (@triffid)

    Well, after staring at the taxonomy schema for half an hour I managed to put together the following query which seems to do what I need.

    SELECT DISTINCT YEAR($wpdb->posts.post_date)
FROM $wpdb->posts, $wpdb->terms, $wpdb->term_taxonomy, $wpdb->term_relationships
WHERE $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'post'
AND $wpdb->terms.term_id = 3
AND $wpdb->terms.term_id = $wpdb->term_taxonomy.term_id
AND $wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id
AND $wpdb->term_relationships.object_id = $wpdb->posts.ID
ORDER BY $wpdb->posts.post_date DESC

    Perhaps someone who knows more about MySQL than me (and that would not be difficult) could tell me if this is a bad idea or not…

Viewing 1 replies (of 1 total)
  • The topic ‘How to determine which years there are posts for in a category?’ is closed to new replies.