vendure/issue-4010-collection-n-plus-one.md

Issue #4010: Collection N+1 Query Performance Fix

GitHub: https://github.com/vendure-ecommerce/vendure/issues/4010

Problem

When listing collections with productVariants { totalItems }, the system executes N+1 queries - one count query per collection. With 4000+ collections, this causes server crashes.

Solution Design

Core Principle

Batch-fetch variant counts for all collections in a single query at the top-level resolver, cache the promise, and let field resolvers await it.

Schema Change

Add a dedicated productVariantCount field to the Collection type instead of relying on productVariants(take: 0) { totalItems } hack:

type Collection {
  # ... existing fields
  productVariantCount: Int!
}

Implementation Components

1. ProductVariantService - Batch Count Method (DONE)

Location: src/service/services/product-variant.service.ts

async getVariantCountsByCollectionIds(
    ctx: RequestContext,
    collectionIds: ID[],
): Promise<Map<ID, number>>

Single query with GROUP BY collection.id returning counts for all requested collections.

2. Top-Level Resolvers - Cache Promise (DONE, needs update)

Locations:

• src/api/resolvers/admin/collection.resolver.ts
• src/api/resolvers/shop/shop-products.resolver.ts

Uses graphql-fields to check if productVariantCount is requested, then caches the promise (not awaited):

const fields = graphqlFields(info);
const itemFields = fields.items ?? {};
if ('productVariantCount' in itemFields) {
    const collectionIds = result.items.map(c => c.id);
    const variantCountsPromise =
        this.productVariantService.getVariantCountsByCollectionIds(ctx, collectionIds);
    this.requestContextCache.set(ctx, COLLECTION_VARIANT_COUNTS_CACHE_KEY, variantCountsPromise);
}

3. Entity Resolver - New Field (TODO)

Location: src/api/resolvers/entity/collection-entity.resolver.ts

Add new productVariantCount field resolver:

@ResolveField()
async productVariantCount(
    @Ctx() ctx: RequestContext,
    @Parent() collection: Collection,
): Promise<number> {
    // Check for cached promise from batch-fetch (list queries)
    const cachedPromise = this.requestContextCache.get<Promise<Map<ID, number>>>(
        ctx,
        COLLECTION_VARIANT_COUNTS_CACHE_KEY,
    );
    if (cachedPromise) {
        const counts = await cachedPromise;
        return counts.get(collection.id) ?? 0;
    }
    // Fallback for single collection queries - fetch individually
    return this.productVariantService.getVariantCountByCollectionId(ctx, collection.id);
}

Remove the take === 0 hack from productVariants resolver.

4. GraphQL Schema (TODO)

Add productVariantCount: Int! to Collection type in:

• src/api/schema/admin-api/collection.api.graphql
• src/api/schema/shop-api/collection.api.graphql (if applicable)

5. ProductVariantService - Single Count Method (TODO)

Add fallback method for single collection queries:

async getVariantCountByCollectionId(ctx: RequestContext, collectionId: ID): Promise<number>

Bug Fixes Applied

RequestContextCacheService Falsy Value Bug (DONE)

Location: src/cache/request-context-cache.service.ts

Fixed get() method - was using if (result) which fails for falsy values like 0. Changed to if (ctxCache.has(key)).

Test Coverage

Location: e2e/collection-n-plus-one.e2e-spec.ts

Tests:

1. N+1 detection - verifies batch query eliminates N+1 pattern
2. Conditional batch-fetch - verifies batch query only runs when productVariantCount requested

Performance Results

Scenario	Queries (Before)	Queries (After)
16 collections with counts	38	4
16 collections without counts	3	3

Remaining Work

1. Add productVariantCount to GraphQL schema
2. Add productVariantCount field resolver
3. Add getVariantCountByCollectionId fallback method
4. Remove take === 0 hack from productVariants resolver
5. Update top-level resolvers to check for productVariantCount instead of productVariants.totalItems
6. Update tests to use new field
7. Run codegen
8. Consider shop API implications (enabled filter for counts?)

Files Modified

• src/service/services/product-variant.service.ts - batch count method
• src/api/resolvers/admin/collection.resolver.ts - promise caching
• src/api/resolvers/shop/shop-products.resolver.ts - promise caching
• src/api/resolvers/entity/collection-entity.resolver.ts - cache key export, needs refactor
• src/cache/request-context-cache.service.ts - falsy value bug fix
• e2e/collection-n-plus-one.e2e-spec.ts - benchmark tests