1

I need to get a list of the years with posts of given category. Like this: 2008, 2009, 2010, 2012, 2013 (if there were no posts in 2011, it's not included).

I try this query, and it works — I get my list of the years:

$wpdb->get_results("SELECT YEAR(post_date) FROM {$wpdb->posts} 
WHERE post_status = 'publish' GROUP BY YEAR(post_date) DESC");

Then I introduce category ID, and nothing works:

$wpdb->get_results("SELECT YEAR(post_date) FROM {$wpdb->posts} 
WHERE post_status = 'publish' AND cat = '4' GROUP BY YEAR(post_date) DESC");

And even simple query with category doesn't work:

$wpdb->get_results("SELECT YEAR(post_date) FROM {$wpdb->posts} WHERE 
cat = '2' GROUP BY YEAR(post_date) DESC");

Category with this ID definitely exists — this is the part of url from the list of categories: term.php?taxonomy=category&tag_ID=2&post_type=post

There are more than a hundred posts it it.

0

This is because the category-relations are not stored within the wp_posts table, but in another table named wp_term_relationships.

You have to do some joins to get what you want. This should give you an array of Years with the published posts in this year.

$years = $wpdb->get_results("SELECT DISTINCT YEAR($wpdb->posts.post_date) as year, COUNT($wpdb->posts.ID) as count FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->term_taxonomy.term_id = 4 AND $wpdb->posts.post_status = 'publish' AND
$wpdb->posts.post_type = 'post' 
GROUP BY YEAR($wpdb->posts.post_date) 
ORDER BY $wpdb->posts.post_date ASC");

Happy Coding!

0

Relationship between post and category is not stored in the posts table. You must extend your query for additional tables.

By category id:

SELECT YEAR(p.post_date) FROM {$wpdb->posts} p 
JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.id 
JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id 
WHERE tt.term_id = 4 AND p.post_status = 'publish' AND p.post_type = 'post'
GROUP BY YEAR(p.post_date) DESC

Or by category slug:

SELECT YEAR(p.post_date) FROM {$wpdb->posts} p 
JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.id 
JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN {$wpdb->terms} t ON t.term_id = tt.term_id  
WHERE t.slug = 'your_category_slug' AND p.post_type = 'post' AND p.post_status = 'publish' 
GROUP BY YEAR(p.post_date) DESC
0

There category is not stored in the wp_posts but in the wp_terms-table. Please have a look here. There query should probably be more like this one:

SELECT YEAR(post_date) FROM wp_posts WHERE post_status = "publish" AND ID IN (
    SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (
        SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = "category" AND term_id = 4
    )
) GROUP BY YEAR(post_date) DESC;

You could use also WP_Query if you need a list the of posts of a category of a specific year:

$params = [ 
    'cat' => 4, 
    'date_query' => [
        [ 'year' => 2016 ],
    ],
    'posts_per_page' => -1,
];
$query = new WP_Query( $params );

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.