Changeset 60788

Timestamp:

09/21/2025 05:25:49 AM (3 weeks 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 );