Spring Data JPA count query for a joined Page

A joined row is not always one receipt

A receipt with three matching lines can appear three times in a SQL join. If the content query selects distinct receipts but the count query counts joined rows, the UI may claim more receipts than exist. Define the count expression explicitly for a Page query that filters on receipt lines. The example counts distinct receipt IDs and keeps the tenant predicate in both queries.

Know when the count is worth it

Count distinct may require extra sorting or hashing in the database, depending on the plan and index. If the screen only needs a next button, a Slice may avoid the total count; still test the joined content query for duplicates and stable boundaries. For a large report where exact totals matter, benchmark the count separately and consider a dedicated read model rather than hiding the expense in a repository method.

Compare content and total

Seed one receipt with multiple matching lines, one with a single matching line, and a matching receipt in another tenant. The first page must contain two distinct local receipts and report totalElements as two. Run the test against the actual database dialect used in deployment. A unit mock cannot expose a wrong JPQL count or provider-specific pagination behavior.

Implementation contract

@Query(
    value = "select distinct receipt from ReceiptEntity receipt " +
        "join receipt.lines line where receipt.tenantId = :tenantId " +
        "and line.sku = :sku",
    countQuery = "select count(distinct receipt.id) from ReceiptEntity receipt " +
        "join receipt.lines line where receipt.tenantId = :tenantId " +
        "and line.sku = :sku")
Page<ReceiptEntity> findByTenantAndLineSku(
    UUID tenantId, String sku, Pageable page);

Cost and verification

The content query and count query are separate database work. DISTINCT can add memory and sort/hash work; an index on line sku and join keys plus a tenant-selective access path may help, but inspect the actual execution plan.

Common Mistakes

• Do not count joined rows when the page displays distinct parent entities.
• Do not forget the tenant predicate in the count query.
• Do not trust in-memory repository doubles to validate JPQL, SQL plans, or page totals.

Read next

Spring Data JPA Page versus Slice: pay for totals only when needed, Spring Data JPA Specifications with a mandatory tenant predicate, Spring JPA fetch plans: measure N+1 queries before changing mappings, Spring Data JPA projections: return selected fields without a full entity, Spring Data keyset pagination: continue after the last stable identifier.

Related data contract

JPA collection fetch and pagination: page IDs before loading children.