Add caching to `count_many_users_posts()`

[flixos90]

Follow up to #39242, specifically as raised in https://core.trac.wordpress.org/ticket/39242#comment:39: As we already added caching to count_user_posts(), it makes sense to add similar caching to count_many_users_posts().

cc @spacedmonkey @peterwilsoncc

Trac ticket: https://core.trac.wordpress.org/ticket/63045

Add proper caching support to count_many_users_posts() function

Enhances the performance of count_many_users_posts() function with working cache key generation.

Trac ticket: https://core.trac.wordpress.org/ticket/63045

[rollybueno]

I'm not a fan of md5() the query statement so I pushed a new patch version in ​https://github.com/WordPress/wordpress-develop/pull/9106/ that:

• More transparent cache keys – avoids md5() and instead uses readable, parameter-based keys (user IDs, post type, $public_only), which makes it easier to debug and trace.
• Proper separation of cached data – different input combinations get unique keys, reducing the risk of stale or incorrect cache results.
• Better cache grouping – avoids using a static group, so it's safer and more aligned with how the query behaves.
• Easier to maintain or extend – the logic is clearer and more flexible for future changes.

Overall, it's a more accurate, debuggable, and scalable approach to caching in count_many_users_posts().

My questions:

1. ❓ Should we have the OC an expiration? If yes, how long? I have set to 1 hour but it's open for discussions.
2. ❓ We probably need cache clearing function, but on what actions it should hook into?

[rollybueno]

During the bi-weekly coffee-hours, this needs more discussion regarding the caching approach, either using readable cache key or using md5.

Props @flixos90

[spacedmonkey]

I strongly believe we should work on #59592 first, as that will effect this ticket's approach.

[sachinrajcp123]

Add object caching to count_many_users_posts() to avoid repeated database queries, similar to count_user_posts().

[kalpeshh]

I checked the PR ​https://github.com/WordPress/wordpress-develop/pull/9106/files and 63045-cache-count_many_users_posts.diff​

The issue with both approaches is different cache_key would be generated if order of array $users is different

$userlist    = implode( ',', array_map( 'absint', $users ) );
$cache_key   = "count_many_users_posts_{$post_type}_{$public_only}_" . str_replace( ',', '_', $userlist ) . '_' . get_current_user_id();

For above code,

$users = [ 1, 2, 3 ] → $userlist = "1,2,3" → _1_2_3_ in the cache key.

$users = [ 3, 2, 1 ] → $userlist = "3,2,1" → _3_2_1_ in the cache key.

To fix this, we should normalize the $users array before building the key:

• Ensure all IDs are integers.
• Remove duplicates.
• Sort the array to guarantee consistent order.

Here is the suggested fix for this,

$users      = array_map( 'absint', (array) $users );
$users      = array_filter( $users );   // remove 0/invalids
$users      = array_unique( $users );   // remove duplicates
sort( $users );                         // enforce consistent order

$userlist   = implode( '_', $users );
$cache_key  = "count_many_users_posts_{$post_type}_{$public_only}_{$userlist}_" . get_current_user_id();

[SirLouen]

Hey @kalpeshh, for your information, needs-refresh means, that the patch is not applying correctly. What I think you were trying to add is changes-requested. I'm adding this for you.

[kalpeshh]

Hi @SirLouen yes my bad. Thanks for updating to changes-requested

[rollybueno]

Thanks for the feedback @kalpeshh! Updated ​https://github.com/WordPress/wordpress-develop/pull/9106.

I just noticed, however, that @spacedmonkey has also requested feedback on ​https://github.com/WordPress/wordpress-develop/pull/8444 , which I only saw after I have updated my branch.

By the way that PR has unit test(still need some polishing) so I' think we should use that instead of mine.

Keeping the existing keywords for ​https://github.com/WordPress/wordpress-develop/pull/8444

[rollybueno]

Update: @peterwilsoncc and @westonruter have reviewed PR ​https://github.com/WordPress/wordpress-develop/pull/9106, and I’ve applied their feedback. The changes include:

1. Switching to md5 for the cache key to handle Memcached’s 250-character limit
2. Replacing with wp_cache_get_salted()
3. Adding a dedicated unit test function specifically for this ticket

Marking the ticket as needs testing.

[wildworks]

This ticket was featured on today's 6.9 Bug Scrub. We need to test the updated PR.

I think the tests could do with a little work to ensure the cache is hit, invalidated correctly and to ensure against public/private collisions.

@rollybueno I'll take the liberty of pushing the changes to your branch as the beta is approaching

@rollybueno I've pushed the tests changes:

The main purpose was to split the tests out in to individual items rather than one big mega test. This is to make it clearer for devs if something starts failing in the future.

For the caching tests, I also added tests for the number of database queries on the second call of the function. This ensures that the cache is hit rather than the database. I did similar for cache invalidation tests, but in those cases a call to the database is expected.

I've also added some tests that equivalent arguments it the cache, eg querying [ posts, pages ] hits the same cache as [ pages, posts ]. For this I discovered a bug that I've pushed a code fix for.

Ignore the failing build processes tests, that's unrelated to this pull request.

Thanks @shail-mehta, I've fixed the ticket references in each of the tests.

[peterwilsoncc]

In 60941:

Users: Add caching to count_many_users_posts().

Introduces object caching to the count_many_users_posts() function.

Argument equivalency is checked prior to generating the cache key to ensure that the same cache is hit regardless of array order for users and post types. For example count_many_users_posts( [ 1, 2 ] ) will hit the same cache as count_many_users_posts( [ 2, 1 ] ).

Props adamsilverstein, flixos90, kalpeshh, rollybueno, sachinrajcp123, shailu25, sirlouen, spacedmonkey, westonruter, wildworks.
Fixes #63045.

[spacedmonkey]

@peterwilsoncc I would tweak the logic here.

   $where       = get_posts_by_author_sql( $post_type, true, null, $public_only );
   $query       = "SELECT post_author, COUNT(*) FROM $wpdb->posts $where AND post_author IN ($userlist) GROUP BY post_author";
   ...
   if ( false === $count ) {
        $result = $wpdb->get_results( $query, ARRAY_N );

Removes duplicate logic generating the SQL query from the function.

Follow up to r60941, ​https://github.com/WordPress/wordpress-develop/commit/bb4d8706d0fea1a836eeed8d097c29368433d017

Trac ticket: https://core.trac.wordpress.org/ticket/63045

[peterwilsoncc]

In 60981:

Users: Remove duplicate query generation from count_many_users_posts().

Removes duplicate code generating the WHERE clause and SQL query from the function.

Follow up to [60941].

Props spacedmonkey.
Fixes #63045.