Categories: counting posts & listing oldest first

Hi jarle,
Don’t know about your second question, but if you’re comfortable modifying a little PHP, an answer to your first question would be to edit the following SQL statement in wp-includes/templates-functions-category.php from:
if (!count($category_posts)) {
$cat_counts = $wpdb->get_results(" SELECT cat_ID,
COUNT($tablepost2cat.post_id) AS cat_count
FROM $tablecategories
INNER JOIN $tablepost2cat ON (cat_ID = category_id)
INNER JOIN $tableposts ON (ID = post_id)
WHERE post_status = 'publish' $exclusions
GROUP BY category_id");
foreach ($cat_counts as $cat_count) {
if (1 != intval($hide_empty) || $cat_count > 0) {
$category_posts["$cat_count->cat_ID"] = $cat_count->cat_count;
}
}
}
To:
if (!count($category_posts)) {
$cat_counts = $wpdb->get_results(" SELECT cat_ID,
COUNT($tablepost2cat.post_id) AS cat_count
FROM $tablecategories
INNER JOIN $tablepost2cat ON (cat_ID = category_id)
INNER JOIN $tableposts ON (ID = post_id)
WHERE post_status = 'publish' and post_date < NOW() $exclusions
GROUP BY category_id");
foreach ($cat_counts as $cat_count) {
if (1 != intval($hide_empty) || $cat_count > 0) {
$category_posts["$cat_count->cat_ID"] = $cat_count->cat_count;
}
}
}
The significant edit is adding “and post_date < NOW() ” to the WHERE condition of the SQL statement. This will ensure that only posts with a post_date less than the current datetime are used to aggregate the count of posts in the categories list.
Note: by making this change if you have a category that only has one or more future-dated posts, that category will not show up in the category list until one of those posts becomes displayable (ie we reach the datetime preset on the post). At least, this is how making the change above works on my WP installation.
Hope this is of some help.
Much warmth,
planetthoughtful

Great! Thanks planetthoughtful!

No problem at all, jarle, you’re very welcome!
I’d like to see this make its way into the WP distro, because I imagine we’re not the only ones disconcerted by a category post count of, say, 2 when only 1 post is actually viewable because the other one is future-dated.
Much warmth,
planetthoughtful

I’m in 1.5 and the code is slightly different. I want it to show ALL dates, past and present.

I just took out the reference to the date in templates-functions-category.php:

WHERE post_status = 'publish'
AND post_date_gmt < '$now' $exclusions

becomes

WHERE post_status = 'publish'

I don’t know if I need “$exclusions”.

To get the Archives to count future dates, I changed the following in template-functions-general:

if ('monthly' == $type) {
$arcresults = $wpdb->get_results("SELECT DISTINCT YEAR(post_date) AS year, MONTH(post_date) AS month, count(ID) as posts FROM $wpdb->posts WHERE post_date > '$now' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC" . $limit);

To:

if ('monthly' == $type) {
$arcresults = $wpdb->get_results("SELECT DISTINCT YEAR(post_date) AS year, MONTH(post_date) AS month, count(ID) as posts FROM $wpdb->posts WHERE post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC" . $limit);