Add user counter functions for topics and replies

[anointed]

I wanted an easy way to display the total number of topics and replies created by my users. This part was rather easy but I have a few issues with what to do when we're allowing a guest to post topics. The counters add up all the topics for all the users and apply it to each user.

Here is my attempt at this script. Please be kind as I know it doesn't work right for guests and there may be much better ways of doing it.

[anointed]

topic and reply counter functions

[johnjamesjacoby]

(In [3632]) Introduce template tags for getting user topic, reply, and total post counts. See #1694.

[johnjamesjacoby]

(In [3633]) Introduce raw functions for getting user topic and reply post counts directly from the posts table. See #1694.

[johnjamesjacoby]

Functions are in, but are not being used anywhere. No time to finish this for 2.1. Punting to 2.2.

[studiograsshopper]

I know this is a work in progress and, therefore, there may be code on it's way that hasn't been committed yet, but I think the topic count should include closed topics, ie where post_status is "closed" as well as "publish". Otherwise, one could have a situation where a user posts his first topic which is later closed by an admin/mod, resulting in the OP's topic count being zero - which doesn't make much sense to me.

To accommodate this, tweaks would be needed to bbp_admin_repair_user_reply_count() and bbp_admin_repair_user_topic_count() in bbp-tools.php.

[jaredatch]

@johnjamesjacoby - this still on track for 2.2?

If so, what's needed? I know the bulk of the functions that handle this are already in, do they just need to be hooked up?

[johnjamesjacoby]

Pretty much. Also the tweak for closed topics above.

[MZAWeb]

I'm using user_meta as a cache for the counts. I'm hooking into all places I care like new reply, new thread, delete reply, delete thread, etc. to keep the user_meta in sync. But had to do this queries to get the counts in a batch (first time, or in the "recounts" menu, in case for some reason the user_meta goes out of sync)

private function batch_fill_replies() {
	global $wpdb;

	$sql_delete = "DELETE FROM `{$wpdb->usermeta}` WHERE `meta_key` = '%s';";
	$sql_delete = $wpdb->prepare( $sql_delete, self::REPLY_COUNT_KEY );
	if ( $wpdb->query( $sql_delete ) === false ) return false;

	$sql = "INSERT INTO `{$wpdb->usermeta}` (user_id, meta_key, meta_value)
						SELECT post_author, '%s', COUNT(id) FROM `{$wpdb->posts}` WHERE post_type IN ('reply') GROUP BY post_author HAVING COUNT(id) > 0";

	$sql = $wpdb->prepare( $sql, self::REPLY_COUNT_KEY );

	if ( $wpdb->query( $sql ) === false ) return false;

	return true;
}

private function batch_fill_topics() {
	global $wpdb;

	$sql_delete = "DELETE FROM `{$wpdb->usermeta}` WHERE `meta_key` = '%s';";
	$sql_delete = $wpdb->prepare( $sql_delete, self::TOPIC_COUNT_KEY );
	if ( $wpdb->query( $sql_delete ) === false ) return false;

	$sql = "INSERT INTO `{$wpdb->usermeta}` (user_id, meta_key, meta_value)
								SELECT post_author, '%s', COUNT(id) FROM `{$wpdb->posts}` WHERE post_type IN ('topic') GROUP BY post_author HAVING COUNT(id) > 0";

	$sql = $wpdb->prepare( $sql, self::TOPIC_COUNT_KEY );

	if ( $wpdb->query( $sql ) === false ) return false;

	return true;
}

[johnjamesjacoby]

The tool recounts does something similar, breaking the queries up into 10000 user batches.

Are you able to patch the bump function into the correct hooks?

[MZAWeb]

There, I implemented the functions and hooked them in.

Had to fix a little issue in bbp_delete_topic. The child replies were not being deleted.

[MZAWeb]

Actually, it's still not deleting the replies. Checking the actual mysql query for that WP_Query in bbp_delete_topic, I see the 'post_status' => 'any' is not being honoured, not sure why. Will create a new ticket.

[MZAWeb]

.2 is a clean diff without messing with bbp_delete_topic

[johnjamesjacoby]

Punting to 2.4, since this needs testing and the patch isn't exactly right.

[johnjamesjacoby]

No time. Moving to 2.5.

[jaredatch]

Definitely want this for 2.5

@jjj what's left here to do or isn't quite right? I can see what I can do :)

[MZAWeb]

+1 How can we help?

[johnjamesjacoby]

Bump to 2.6.

[netweb]

Related #2519

[netweb]

Refreshed ​1694.3.diff

• Use update_user_option to update the counts (mulsisite compat) rather than update_user_meta

• Also fixes incorrect bbp_get_user_reply_count $filter variable

Testing:

• New Topic increases wp__bbp_topic_count usermeta by 1 (Where wp_ is the current $WPDB $Prefix)
• New Reply increases wp__bbp_reply_count usermeta by 1 (Where wp_ is the current $WPDB $Prefix)

• Trash Topic wp__bbp_topic_count is not updated
• Trash Reply wp__bbp_reply_count is not updated

• Spam Topic wp__bbp_topic_count is not updated
• Spam Reply wp__bbp_reply_count is not updated

• Delete Topic decreases wp__bbp_topic_count usermeta by 1 (Where wp_ is the current $WPDB $Prefix)
• Delete Reply decreases wp__bbp_reply_count usermeta by 1 (Where wp_ is the current $WPDB $Prefix)

Presuming we should switch to subtracting the count when a topic or reply is trashed or marked as spam otherwise the spam and trash topics remain counted in the user counts.

[netweb]

Woops ;)

[netweb]

Also 'Forum Freshness' is not updated when using 1694.3.diff.

[netweb]

Replying to netweb:

Also 'Forum Freshness' is not updated when using 1694.3.diff.

Ignore this, I'll create a separate ticket for this once I can explain it clearly.

[netweb]

Replying to netweb:

Presuming we should switch to subtracting the count when a topic or reply is trashed or marked as spam otherwise the spam and trash topics remain counted in the user counts.

The above in ​1694.4.diff switched to decreasing the count when a topic or reply is spammed or trashed.

Testing:

• New Topic increases wp__bbp_topic_count usermeta by 1 (Where wp_ is the current $WPDB $Prefix)
• New Reply increases wp__bbp_reply_count usermeta by 1 (Where wp_ is the current $WPDB $Prefix)

• Trash Topic decreases wp__bbp_topic_count usermeta by 1 (Where wp_ is the current $WPDB $Prefix)
• Trash Reply decreases wp__bbp_reply_count usermeta by 1 (Where wp_ is the current $WPDB $Prefix)

• Spam Topic decreases wp__bbp_topic_count usermeta by 1 (Where wp_ is the current $WPDB $Prefix)
• Spam Reply decreases wp__bbp_reply_count usermeta by 1 (Where wp_ is the current $WPDB $Prefix)

• Delete Topic wp__bbp_topic_count is not updated
• Delete Reply wp__bbp_reply_count is not updated

[johnjamesjacoby]

In 5309:

Introduce topic & reply counts for users:

• New functions for updating user options.
• New action hooks for bumping existing counts on CRUD actions.
• Uses existing _raw functions when no counts are previously found to incrementally update missing meta values.
• Tool for updating topic & reply counts already exists.
• Props netweb, MZAWeb, anointed.
• Fixes #1694.

[wpdennis]

If a user posts his very first post, let's say a reply, it will set his post count to 2 instead of 1, because:

bbp_bump_user_reply_count() bumps it by one. But since

$count = bbp_get_user_reply_count( $user_id, true );

returns 0 for a new user (usermeta _bbp_reply_count not available), it counts the existing posts:

if ( empty( $count ) ) { 
    $count = bbp_get_user_reply_count_raw( $user_id ); 
} 

which is 1 at this point. This gets increased by one afterwards resulting in a post count of 2.

( (int) $count + (int) $difference )

But this isn't a "first post bug". If for any reason the usermeta (_bbp_reply_count or _bbp_topic_count) gets deleted, it will miscalculate again, as far as I can tell.

[netweb]

Replying to wpdennis:

If a user posts his very first post, let's say a reply, it will set his post count to 2 instead of 1, because:

bbp_bump_user_reply_count() bumps it by one. But since

$count = bbp_get_user_reply_count( $user_id, true );

returns 0 for a new user (usermeta _bbp_reply_count not available), it counts the existing posts:

if ( empty( $count ) ) { 
    $count = bbp_get_user_reply_count_raw( $user_id ); 
} 

which is 1 at this point. This gets increased by one afterwards resulting in a post count of 2.

( (int) $count + (int) $difference )

Confirmed

But this isn't a "first post bug". If for any reason the usermeta (_bbp_reply_count or _bbp_topic_count) gets deleted, it will miscalculate again, as far as I can tell.

This is pretty much true for any of bbPress counts as we rely on storing counts in post meta, these are what the repair tools are for.

[netweb]

Marking as fixed, the ​1799.5.patch in #1799 resolves the outstanding issue above, and related ​discussion in Slack.