Changeset 60788

Timestamp:

09/21/2025 05:25:49 AM (11 months ago)

Author:

westonruter

Message:

Posts, Post Types: Improve wp_count_posts() query performance for users who cannot read_private_posts.

The query is refactored to use two subqueries which can leverage DB indexes.

Props rcorrales, snehapatil02, sirlouen, sajjad67, pbearne, johnbillion, westonruter.
Fixes #61097.

Location:

trunk

Files:

• src/wp-includes/post.php (modified) (1 diff)
• tests/phpunit/tests/post.php (modified) (8 diffs)

trunk/src/wp-includes/post.php

@@ -3401 +3401 @@
         }
 
-        $query = "SELECT post_status, COUNT( * ) AS num_posts FROM {$wpdb->posts} WHERE post_type = %s";
-
-        if ( 'readable' === $perm && is_user_logged_in() ) {
-                $post_type_object = get_post_type_object( $type );
-                if ( ! current_user_can( $post_type_object->cap->read_private_posts ) ) {
-                        $query .= $wpdb->prepare(
-                                " AND (post_status != 'private' OR ( post_author = %d AND post_status = 'private' ))",
-                                get_current_user_id()
-                        );
-                }
-        }
-
-        $query .= ' GROUP BY post_status';
-
-        $results = (array) $wpdb->get_results( $wpdb->prepare( $query, $type ), ARRAY_A );
+        if (
+                'readable' === $perm &&
+                is_user_logged_in() &&
+                ! current_user_can( get_post_type_object( $type )->cap->read_private_posts )
+        ) {
+                // Optimized query uses subqueries which can leverage DB indexes for better performance. See #61097.
+                $query = $wpdb->prepare(
+                        "
+                        SELECT post_status, COUNT(*) AS num_posts
+                        FROM (
+                                SELECT post_status
+                                FROM {$wpdb->posts}
+                                WHERE post_type = %s AND post_status != 'private'
+                                UNION ALL
+                                SELECT post_status
+                                FROM {$wpdb->posts}
+                                WHERE post_type = %s AND post_status = 'private' AND post_author = %d
+                        ) AS filtered_posts
+                        ",
+                        $type,
+                        $type,
+                        get_current_user_id()
+                );
+        } else {
+                $query = $wpdb->prepare(
+                        "
+                        SELECT post_status, COUNT(*) AS num_posts
+                        FROM {$wpdb->posts}
+                        WHERE post_type = %s
+                        ",
+                        $type
+                );
+        }
+
+        $query  .= ' GROUP BY post_status';
+        $results = (array) $wpdb->get_results( $query, ARRAY_A );
         $counts  = array_fill_keys( get_post_stati(), 0 );
 

trunk/tests/phpunit/tests/post.php

@@ -7 +7 @@
  */
 class Tests_Post extends WP_UnitTestCase {
-        protected static $editor_id;
         protected static $grammarian_id;
 
+        protected static $user_ids = array(
+                'administrator' => null,
+                'editor'        => null,
+                'author'        => null,
+                'contributor'   => null,
+        );
+
         private $post_ids = array();
 
         public static function wpSetUpBeforeClass( WP_UnitTest_Factory $factory ) {
-                self::$editor_id = $factory->user->create( array( 'role' => 'editor' ) );
+
+                self::$user_ids = array(
+                        'administrator' => $factory->user->create(
+                                array(
+                                        'role' => 'administrator',
+                                )
+                        ),
+                        'editor'        => $factory->user->create(
+                                array(
+                                        'role' => 'editor',
+                                )
+                        ),
+                        'author'        => $factory->user->create(
+                                array(
+                                        'role' => 'author',
+                                )
+                        ),
+                        'contributor'   => $factory->user->create(
+                                array(
+                                        'role' => 'contributor',
+                                )
+                        ),
+                );
 
                 add_role(
@@ -143 +171 @@
         /**
          * @ticket 24803
+         * @covers ::wp_count_posts
          */
         public function test_wp_count_posts() {
@@ -162 +191 @@
         }
 
+        /**
+         * Ensure `wp_count_posts()` in 'readable' context excludes private posts
+         * authored by other users when the current user lacks the capability to
+         * read private posts.
+         *
+         * @ticket 61097
+         * @covers ::wp_count_posts
+         */
+        public function test_wp_count_posts_readable_excludes_unreadable_private_posts() {
+                $post_type = rand_str( 20 );
+                register_post_type( $post_type );
+
+                $admin_user_id = self::$user_ids['administrator'];
+
+                self::factory()->post->create_many(
+                        5,
+                        array(
+                                'post_type'   => $post_type,
+                                'post_status' => 'publish',
+                                'post_author' => $admin_user_id,
+                        )
+                );
+
+                self::factory()->post->create_many(
+                        3,
+                        array(
+                                'post_type'   => $post_type,
+                                'post_status' => 'private',
+                                'post_author' => $admin_user_id,
+                        )
+                );
+
+                $current_user_id = self::$user_ids['author'];
+                wp_set_current_user( $current_user_id );
+
+                $count = wp_count_posts( $post_type, 'readable' );
+                $this->assertEquals( 5, $count->publish );
+                _unregister_post_type( $post_type );
+        }
+
+        /**
+         * @covers ::wp_count_posts
+         */
         public function test_wp_count_posts_filtered() {
                 $post_type = rand_str( 20 );
@@ -187 +259 @@
         }
 
+        /**
+         * @covers ::wp_count_posts
+         */
         public function test_wp_count_posts_insert_invalidation() {
                 $post_ids       = self::factory()->post->create_many( 3 );
@@ -207 +282 @@
         }
 
+        /**
+         * @covers ::wp_count_posts
+         */
         public function test_wp_count_posts_trash_invalidation() {
                 $post_ids       = self::factory()->post->create_many( 3 );
@@ -227 +305 @@
         /**
          * @ticket 49685
+         * @covers ::wp_count_posts
          */
         public function test_wp_count_posts_status_changes_visible() {
@@ -253 +332 @@
                 wp_set_object_terms( $post, 'foo', $tax );
 
-                wp_set_current_user( self::$editor_id );
+                wp_set_current_user( self::$user_ids['editor'] );
 
                 $wp_tag_cloud = wp_tag_cloud(
@@ -306 +385 @@
                 );
 
-                wp_set_current_user( self::$editor_id );
+                wp_set_current_user( self::$user_ids['editor'] );
 
                 edit_post( $data );