A Revisions-Count REST Field Without the N+1 Tax

Posted on by Ian Lambert

A Revisions-Count REST Field Without the N+1 Tax

Symptom. A custom revision_count field appears in /wp-json/wp/v2/posts. The response is correct. Query Monitor, however, shows one SELECT COUNT(*) against wp_posts per post in the collection — ten posts, ten counts; a hundred posts, a hundred counts. The endpoint is not slow enough to page anyone at 3 a.m., which is precisely why it survives until the archive page starts rendering a hundred rows.

Trace. The field was registered with register_rest_field() and a get_callback that calls a counting function. The callback contract is the problem: register_rest_field() documents that the get callback “will be passed the prepared object data” — one prepared post at a time. The REST server invokes that callback once for each item it serializes. A query inside the callback therefore runs once per item. That is the N+1 shape, and no amount of cleverness inside the callback removes it, because the callback never sees the collection.

Root cause. Per-item work placed in a per-item hook. The fix is not a faster count; it is moving the count out of the callback and into a batched step that runs once per request, keyed on the IDs the request is about to serialize. The callback then reads from a prebuilt map.

What register_rest_field() actually gives you

The function registers a field on an existing object type and accepts three arguments that matter here: get_callback, update_callback, and schema. The documentation is explicit that the get callback receives the prepared object data, and that omitting update_callback makes the field read-only. It also notes that fields should be registered on rest_api_init, not init, so registration does not run on requests that never touch the REST API.

That is the whole surface. There is no collection-level callback, no “before serialize” hook in the registration arguments, and no batch parameter. If you want batching, you supply it yourself.

The naive field, and its query shape

Here is the version that produces the symptom. It is deliberately ordinary.

add_action( 'rest_api_init', function () {
    register_rest_field(
        'post',
        'revision_count',
        array(
            'get_callback' => function ( $post_arr ) {
                global $wpdb;
                return (int) $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT COUNT(*) FROM {$wpdb->posts}
                         WHERE post_parent = %d
                           AND post_type = 'revision'
                           AND post_status = 'inherit'",
                        $post_arr['id']
                    )
                );
            },
            'schema' => array(
                'description' => 'Number of stored revisions for the post.',
                'type'        => 'integer',
                'context'     => array( 'view', 'edit' ),
                'readonly'    => true,
            ),
        )
    );
} );

Two things are correct here and should stay: the value is cast to an integer, and the post ID goes through $wpdb->prepare() with a %d placeholder. The wpdb documentation is unambiguous that all untrusted values in a SQL statement must be escaped, and that prepare() accepts %s, %d, and %f placeholders. The post ID is not attacker-controlled in most requests, but “most” is not a security posture.

What is wrong is the placement. The callback is invoked per item, so the query is per item. The count itself is cheap on a small site and linear on a large one; the cost is not the individual COUNT, it is the round trips multiplied by page size.

The batching move

The collection’s post IDs are known before the per-item callbacks run. The REST posts controller builds a WP_Query, and the resulting post objects are what get prepared and serialized. If you can observe the query results once, you can compute a map of post_id => revision_count in a single grouped query, stash it in a request-scoped variable, and have the per-item callback read from that map.

The hook that makes this practical is the_posts, which fires after a WP_Query has fetched its posts. It receives the array of posts and the query object. You can inspect the query to decide whether it is a REST posts collection, collect the IDs, run one grouped query, and store the result.

/**
 * Request-scoped map of post ID => revision count.
 *
 * @var array<int,int>
 */
function jooom_revision_count_map( ?array $set = null ) {
    static $map = array();
    if ( null !== $set ) {
        $map = $set;
    }
    return $map;
}

add_action( 'the_posts', function ( $posts, $query ) {
    if ( ! $query->get( 'rest_route' ) && ! ( defined( 'REST_REQUEST' ) && REST_REQUEST ) ) {
        return;
    }
    if ( empty( $posts ) ) {
        return;
    }

    $ids = array();
    foreach ( $posts as $post ) {
        if ( $post instanceof WP_Post && 'revision' !== $post->post_type ) {
            $ids[] = (int) $post->ID;
        }
    }
    if ( empty( $ids ) ) {
        return;
    }

    global $wpdb;
    $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
    $sql = $wpdb->prepare(
        "SELECT post_parent, COUNT(*) AS revision_count
         FROM {$wpdb->posts}
         WHERE post_parent IN ($placeholders)
           AND post_type = 'revision'
           AND post_status = 'inherit'
         GROUP BY post_parent",
        $ids
    );

    $rows = $wpdb->get_results( $sql, ARRAY_A );
    $map  = array_fill_keys( $ids, 0 );
    foreach ( $rows as $row ) {
        $map[ (int) $row['post_parent'] ] = (int) $row['revision_count'];
    }

    jooom_revision_count_map( $map );
}, 10, 2 );

Then the field callback becomes a lookup:

'get_callback' => function ( $post_arr ) {
    $map = jooom_revision_count_map();
    $id  = (int) $post_arr['id'];
    return isset( $map[ $id ] ) ? $map[ $id ] : 0;
},

Three details keep this honest. First, the map is initialized with zero for every requested ID, so posts with no revisions return 0 rather than null or a missing key. Second, the grouped query is driven by the collection’s IDs, so its cost scales with page size, not with the size of the posts table. Third, the IN list is built from placeholders and passed through prepare(); the wpdb documentation warns that % characters inside SQL string literals must be double-escaped, which is why the query avoids LIKE entirely.

One caveat about the hook: the_posts fires for every WP_Query, including queries that have nothing to do with the REST request. The guard above checks REST_REQUEST or the rest_route query var, but a plugin that runs its own WP_Query during a REST request will also trigger the batch. That is wasted work, not incorrect data, because the map is keyed by ID and the callback only reads IDs it asked for. If you want to be stricter, check $query->get( 'post_type' ) against the post types whose REST controllers you registered the field on.

Why not just call a core helper?

There is a natural instinct to reach for a core function that counts revisions. The retrieved sources for this article do not include a fetched page documenting such a helper’s signature, so this article does not assert one exists or recommend calling it. The batched query above is presented as an illustrative shape, not as a claim about how core implements revision counting internally. If you do find a core helper that accepts a post ID and returns a count, it will still be a per-post call, and the N+1 problem returns. The batching is the point, not the specific SQL.

The revisions-specific wrinkle

Revisions are posts. The core revisions controller queries them with post_parent set to the parent ID, post_type set to 'revision', and post_status set to 'inherit', and it only does so after checking wp_revisions_enabled( $parent ). It also exposes the total revision count in the X-WP-Total response header for the revisions collection endpoint.

That header is useful context but not a substitute for the field. It belongs to a different request — /wp/v2/posts/<id>/revisions — and reading it would require a second HTTP round trip per post, which is a worse N+1 than the one you started with. The custom field exists so a collection response can carry the count inline. Do not try to reuse the revisions controller’s internals; treat it as a separate endpoint with its own contract.

There is also a correctness question the field should answer explicitly: does “revision count” include autosaves? Autosaves are stored as revisions with a post_name containing -autosave, and core’s save routine skips them when pruning. The query above counts every row with post_type = 'revision' and post_status = 'inherit', which includes autosaves. If your editorial workflow treats autosaves as noise, add AND post_name NOT LIKE '%%-autosave%%' — note the doubled percent signs required by prepare() — and document the choice in the field description. Either answer is defensible; an undocumented one is not.

Schema and read-only discipline

The schema should say what the field is: an integer, described, and read-only. Omitting update_callback is what makes it read-only, per the REST API handbook. A revision count is derived data; accepting writes to it would invite clients to set a number that core’s revision system will immediately contradict.

The handbook also draws a line worth repeating: changing or removing data from core REST responses can break plugins or core behavior and should be avoided, while adding fields is described as not dangerous. A new revision_count field is an addition. Do not “improve” it by filtering rest_prepare_post to rewrite an existing core field.

Verification

Query Monitor is the instrument. Load /wp-json/wp/v2/posts?per_page=20 with the plugin active and filter the queries panel to wp_posts. Before the fix you should see one SELECT COUNT(*) per post, all with the same shape and different post_parent values. After the fix you should see a single grouped query with an IN (...) list and a GROUP BY post_parent, plus the normal posts query. The count of COUNT(*) queries should be one, not twenty.

From the command line, the same check without a browser:

wp eval '
$request = new WP_REST_Request( "GET", "/wp/v2/posts" );
$request->set_param( "per_page", 20 );
$response = rest_do_request( $request );
$data = $response->get_data();
foreach ( $data as $post ) {
    echo $post["id"] . " => " . $post["revision_count"] . PHP_EOL;
}
'

Run it with and without the batching hook and compare the query log. If your site has no revisions on the sampled posts, seed a few with wp post update <id> --post_content="..." before measuring, or the test proves only that zero equals zero.

Finally, confirm the field is absent from non-REST requests. Because registration is on rest_api_init, a normal page load should not run the registration callback at all. If it does, something is calling register_rest_field() on init, and that is a separate bug worth fixing while you are in the file.

Checklist

  • Register on rest_api_init, not init.
  • Keep the per-item callback free of queries; it reads from a request-scoped map.
  • Build the map with one grouped query keyed on the collection’s IDs.
  • Use $wpdb->prepare() with placeholders for every value, including the IN list.
  • Declare the field as an integer with a description and no update_callback.
  • Decide explicitly whether autosaves count, and say so in the schema description.
  • Verify with Query Monitor or wp eval that the number of COUNT queries equals one, not the page size.

FAQ

Does register_rest_field() have a batch callback?

No. The documented arguments are get_callback, update_callback, and schema. The get callback receives one prepared object at a time. Batching has to be supplied by the surrounding code.

Can I cache the counts in a transient instead?

You can, but a revision count changes on every save, so the transient would need invalidation on every revision creation and deletion. A request-scoped map avoids that problem entirely: it is computed once per request from current data and discarded. Persistent caching is a different optimization with a different failure mode — stale counts — and should be a deliberate choice, not a default.

Why not query the revisions endpoint and read X-WP-Total?

Because that is one HTTP request per post, which is a worse version of the same N+1. The header is useful when you are already fetching a revisions collection; it is not a substitute for an inline field on the posts collection.

Is the the_posts hook the only place to batch this?

No. It is a convenient place because it sees the fetched posts before serialization. A custom rest_{$post_type}_query filter or a controller-level preparation step could also work, but those are more invasive and more likely to collide with other plugins. The principle is the same: compute once per request, read per item.

What if the field is registered on a custom post type?

The registration and the query are unchanged; only the object type string passed to register_rest_field() changes. The guard in the batching hook should be widened to include that post type, or the map will be empty and every post will report zero.