Repository: ponder-sh/ponder
Pull request #1259 (2024-11-19 20:20:45)

GraphQL `totalCount`
Adds `totalCount` field to GraphQL page type.

Also fixes two bugs that I noticed while implementing this:
- The one-to-many relation query path had a bug where the `orderBy` argument passed to the plural resolver attempted to use the columns of the parent table. Added a test for this, which was failing before the fix.
- The top-level singular resolver was not using dataloader. Now, certain query paths that do self-joins should do one fewer query.


Patch:
diff --git a/.changeset/violet-guests-obey.md b/.changeset/violet-guests-obey.md
new file mode 100644
index 00000000..367044c3
--- /dev/null
+++ b/.changeset/violet-guests-obey.md
@@ -0,0 +1,5 @@
+---
+"@ponder/core": patch
+---
+
+Added `totalCount` field to the plural GraphQL connection type, which returns the total number of records in the database that match the specified `where` clause. [Read more](https://ponder.sh/docs/query/graphql#total-count).
diff --git a/docs/pages/docs/query/graphql.mdx b/docs/pages/docs/query/graphql.mdx
index 084114b0..9d1e3967 100644
--- a/docs/pages/docs/query/graphql.mdx
+++ b/docs/pages/docs/query/graphql.mdx
@@ -60,8 +60,8 @@ type person {
 type personPage {
   items: [person!]!
   pageInfo: PageInfo!
+  totalCount: Int!
 }
-
 ```
 
 </div>
@@ -235,15 +235,9 @@ query {
 
 ## Pagination
 
-The GraphQL API supports cursor pagination using an API that's inspired by the [Relay GraphQL Cursor Connection](https://relay.dev/graphql/connections.htm#sec-undefined.PageInfo) specification.
-
-- Cursor values are opaque strings that encode the position of a record in the result set. They should not be decoded or manipulated by the client.
-- Cursors are exclusive, meaning that the record at the specified cursor is not included in the result.
-- Cursor pagination works with any valid filter and sort criteria. However, do not change the filter or sort criteria between paginated requests. This will cause validation errors or incorrect results.
+The GraphQL API supports cursor pagination with an API that's inspired by the [Relay GraphQL Cursor Connection](https://relay.dev/graphql/connections.htm#sec-undefined.PageInfo) specification.
 
-### Page type
-
-Top-level plural query fields and `p.many()` relationship fields return a `Page` type containing a list of items and a `PageInfo` object.
+Plural fields and `p.many()` relationship fields each return a `Page` type. This object contains a list of items, a `PageInfo` object, and the total count of records that match the query.
 
 <div className="code-columns">
 ```ts filename="ponder.schema.ts"
@@ -256,12 +250,11 @@ export const pet = onchainTable("pet", (t) => ({
 ```
 
 {/* prettier-ignore */}
-```graphql filename="Generated schema" {13-16}
-type PageInfo {
-  startCursor: String
-  endCursor: String
-  hasPreviousPage: Boolean!
-  hasNextPage: Boolean!
+```graphql filename="Generated schema" {1-5}
+type petPage {
+  items: [pet!]!
+  pageInfo: PageInfo!
+  totalCount: Int!
 }
 
 type pet {
@@ -269,15 +262,19 @@ type pet {
   name: String!
 }
 
-type petPage {
-  items: [pet!]!
-  pageInfo: PageInfo!
+type PageInfo {
+  startCursor: String
+  endCursor: String
+  hasPreviousPage: Boolean!
+  hasNextPage: Boolean!
 }
 ```
 
 </div>
 
-Here is a detailed view of the `PageInfo` object:
+### Page info
+
+The `PageInfo` object contains information about the position of the current page within the result set.
 
 | name                | type                 |                                                 |
 | :------------------ | :------------------- | :---------------------------------------------- |
@@ -286,6 +283,24 @@ Here is a detailed view of the `PageInfo` object:
 | **hasPreviousPage** | `Boolean!{:graphql}` | Whether there are more records before this page |
 | **hasNextPage**     | `Boolean!{:graphql}` | Whether there are more records after this page  |
 
+### Total count
+
+The `totalCount` field returns the number of records present in the database that match the specified query. The value the same value regardless of the current pagination position and the `limit` argument. Only the `where` argument can change the value of `totalCount`.
+
+<Callout type="warning">
+  The SQL query that backs `totalCount` can be slow. To avoid performance
+  issues, include `totalCount` in the query for the first page, then exclude it
+  for subsequent pages. Unless the underlying data has changed, the value will
+  be the same regardless of the pagination position.
+</Callout>
+
+### Cursor values
+
+A cursor value is an opaque string that encodes the position of a record in the result set.
+
+- Cursor values should not be decoded or manipulated by the client. The only valid use of a cursor value is an argument, e.g. `after: previousPage.endCursor{:ts}`.
+- Cursor pagination works with any filter and sort criteria. However, do not change the filter or sort criteria between paginated requests. This will cause validation errors or incorrect results.
+
 ### Examples
 
 As a reminder, assume that these records exist in your database for the following examples.
@@ -318,6 +333,7 @@ query {
       hasPreviousPage
       hasNextPage
     }
+    totalCount
   }
 }
 ```
@@ -335,7 +351,8 @@ query {
       "endCursor": "Mxhc3NDb3JlLTA=",
       "hasPreviousPage": false,
       "hasNextPage": true,
-    }
+    },
+    "totalCount": 4,
   }
 }
 ```
@@ -363,6 +380,7 @@ query {
       hasPreviousPage
       hasNextPage
     }
+    totalCount
   }
 }
 ```
@@ -380,7 +398,8 @@ query {
       "endCursor": "McSDfVIiLka==",
       "hasPreviousPage": true,
       "hasNextPage": false,
-    }
+    },
+    "totalCount": 4,
   }
 }
 ```
@@ -408,6 +427,7 @@ query {
       hasPreviousPage
       hasNextPage
     }
+    totalCount
   }
 }
 ```
@@ -424,7 +444,8 @@ query {
       "endCursor": "Mxhc3NDb3JlLTA=",
       "hasPreviousPage": true,
       "hasNextPage": true,
-    }
+    },
+    "totalCount": 4,
   }
 }
 ```
diff --git a/packages/core/src/graphql/index.ts b/packages/core/src/graphql/index.ts
index ab43152d..ae425a40 100644
--- a/packages/core/src/graphql/index.ts
+++ b/packages/core/src/graphql/index.ts
@@ -12,6 +12,7 @@ import {
   arrayContained,
   arrayContains,
   asc,
+  count,
   createTableRelationsHelpers,
   desc,
   eq,
@@ -36,7 +37,6 @@ import {
   PgSerial,
   isPgEnum,
 } from "drizzle-orm/pg-core";
-import type { RelationalQueryBuilder } from "drizzle-orm/pg-core/query-builders/query";
 import {
   GraphQLBoolean,
   GraphQLEnumType,
@@ -51,6 +51,7 @@ import {
   GraphQLNonNull,
   GraphQLObjectType,
   type GraphQLOutputType,
+  type GraphQLResolveInfo,
   GraphQLScalarType,
   GraphQLSchema,
   GraphQLString,
@@ -213,7 +214,7 @@ export function buildGraphQLSchema(schema: Schema): GraphQLSchema {
             referencedEntityFilterType === undefined
           )
             throw new Error(
-              `Internal error: Referenced entity type not found for table "${referencedTable.tsName}" `,
+              `Internal error: Referenced entity types not found for table "${referencedTable.tsName}" `,
             );
 
           if (is(relation, One)) {
@@ -231,7 +232,7 @@ export function buildGraphQLSchema(schema: Schema): GraphQLSchema {
               type: relation.isNullable
                 ? new GraphQLNonNull(referencedEntityType)
                 : referencedEntityType,
-              resolve: async (parent, _args, context) => {
+              resolve: (parent, _args, context) => {
                 const loader = context.getDataLoader({
                   table: referencedTable,
                 });
@@ -250,7 +251,7 @@ export function buildGraphQLSchema(schema: Schema): GraphQLSchema {
                 }
                 const encodedId = encodeRowFragment(rowFragment);
 
-                return await loader.load(encodedId);
+                return loader.load(encodedId);
               },
             };
           } else if (is(relation, Many)) {
@@ -280,7 +281,7 @@ export function buildGraphQLSchema(schema: Schema): GraphQLSchema {
                 after: { type: GraphQLString },
                 limit: { type: GraphQLInt },
               },
-              resolve: async (parent, args: PluralArgs, context) => {
+              resolve: (parent, args: PluralArgs, context, info) => {
                 const relationalConditions = [];
                 for (let i = 0; i < references.length; i++) {
                   const column = fields[i]!;
@@ -288,16 +289,16 @@ export function buildGraphQLSchema(schema: Schema): GraphQLSchema {
                   relationalConditions.push(eq(column, value));
                 }
 
-                const baseQuery = context.drizzle.query[referencedTable.tsName];
-                if (!baseQuery)
-                  throw new Error(
-                    `Internal error: Referenced table "${referencedTable.tsName}" not found in RQB`,
-                  );
+                const includeTotalCount = selectionIncludesField(
+                  info,
+                  "totalCount",
+                );
 
                 return executePluralQuery(
-                  table,
-                  baseQuery,
+                  referencedTable,
+                  context.drizzle,
                   args,
+                  includeTotalCount,
                   relationalConditions,
                 );
               },
@@ -322,6 +323,7 @@ export function buildGraphQLSchema(schema: Schema): GraphQLSchema {
           ),
         },
         pageInfo: { type: new GraphQLNonNull(GraphQLPageInfo) },
+        totalCount: { type: new GraphQLNonNull(GraphQLInt) },
       }),
     });
   }
@@ -351,20 +353,13 @@ export function buildGraphQLSchema(schema: Schema): GraphQLSchema {
         ]),
       ),
       resolve: async (_parent, args, context) => {
-        const baseQuery = context.drizzle.query[table.tsName];
-        if (!baseQuery)
-          throw new Error(
-            `Internal error: Table "${table.tsName}" not found in RQB`,
-          );
+        const loader = context.getDataLoader({ table });
 
         // The `args` object here should be a valid `where` argument that
         // uses the `eq` shorthand for each primary key column.
-        const whereConditions = buildWhereConditions(args, table.columns);
+        const encodedId = encodeRowFragment(args);
 
-        const row = await baseQuery.findFirst({
-          where: and(...whereConditions),
-        });
-        return row ?? null;
+        return loader.load(encodedId);
       },
     };
 
@@ -378,14 +373,15 @@ export function buildGraphQLSchema(schema: Schema): GraphQLSchema {
         after: { type: GraphQLString },
         limit: { type: GraphQLInt },
       },
-      resolve: async (_parent, args: PluralArgs, context) => {
-        const baseQuery = context.drizzle.query[table.tsName];
-        if (!baseQuery)
-          throw new Error(
-            `Internal error: Table "${table.tsName}" not found in RQB`,
-          );
-
-        return executePluralQuery(table, baseQuery, args);
+      resolve: async (_parent, args: PluralArgs, context, info) => {
+        const includeTotalCount = selectionIncludesField(info, "totalCount");
+
+        return executePluralQuery(
+          table,
+          context.drizzle,
+          args,
+          includeTotalCount,
+        );
       },
     };
   }
@@ -530,10 +526,16 @@ const filterOperators = {
 
 async function executePluralQuery(
   table: TableRelationalConfig,
-  baseQuery: RelationalQueryBuilder<any, any>,
+  drizzle: Drizzle<{ [key: string]: OnchainTable }>,
   args: PluralArgs,
+  includeTotalCount: boolean,
   extraConditions: (SQL | undefined)[] = [],
 ) {
+  const rawTable = drizzle._.fullSchema[table.tsName];
+  const baseQuery = drizzle.query[table.tsName];
+  if (rawTable === undefined || baseQuery === undefined)
+    throw new Error(`Internal error: Table "${table.tsName}" not found in RQB`);
+
   const limit = args.limit ?? DEFAULT_LIMIT;
   if (limit > MAX_LIMIT) {
     throw new Error(`Invalid limit. Got ${limit}, expected <=${MAX_LIMIT}.`);
@@ -564,22 +566,33 @@ async function executePluralQuery(
   const after = args.after ?? null;
   const before = args.before ?? null;
 
+  if (after !== null && before !== null) {
+    throw new Error("Cannot specify both before and after cursors.");
+  }
+
   let startCursor = null;
   let endCursor = null;
   let hasPreviousPage = false;
   let hasNextPage = false;
 
-  if (after !== null && before !== null) {
-    throw new Error("Cannot specify both before and after cursors.");
-  }
+  const totalCountPromise = includeTotalCount
+    ? drizzle
+        .select({ count: count() })
+        .from(rawTable)
+        .where(and(...whereConditions, ...extraConditions))
+        .then((rows) => rows[0]?.count ?? null)
+    : Promise.resolve(null);
 
   // Neither cursors are specified, apply the order conditions and execute.
   if (after === null && before === null) {
-    const rows = await baseQuery.findMany({
-      where: and(...whereConditions, ...extraConditions),
-      orderBy,
-      limit: limit + 1,
-    });
+    const [rows, totalCount] = await Promise.all([
+      baseQuery.findMany({
+        where: and(...whereConditions, ...extraConditions),
+        orderBy,
+        limit: limit + 1,
+      }),
+      totalCountPromise,
+    ]);
 
     if (rows.length === limit + 1) {
       rows.pop();
@@ -595,6 +608,7 @@ async function executePluralQuery(
 
     return {
       items: rows,
+      totalCount,
       pageInfo: { hasNextPage, hasPreviousPage, startCursor, endCursor },
     };
   }
@@ -609,21 +623,20 @@ async function executePluralQuery(
       cursorObject,
     );
 
-    const rows = await baseQuery.findMany({
-      where: and(...whereConditions, cursorCondition, ...extraConditions),
-      orderBy,
-      limit: limit + 2,
-    });
+    const [rows, totalCount] = await Promise.all([
+      baseQuery.findMany({
+        where: and(...whereConditions, cursorCondition, ...extraConditions),
+        orderBy,
+        limit: limit + 2,
+      }),
+      totalCountPromise,
+    ]);
 
     if (rows.length === 0) {
       return {
         items: rows,
-        pageInfo: {
-          hasNextPage,
-          hasPreviousPage,
-          startCursor,
-          endCursor,
-        },
+        totalCount,
+        pageInfo: { hasNextPage, hasPreviousPage, startCursor, endCursor },
       };
     }
 
@@ -654,6 +667,7 @@ async function executePluralQuery(
 
     return {
       items: rows,
+      totalCount,
       pageInfo: { hasNextPage, hasPreviousPage, startCursor, endCursor },
     };
   }
@@ -669,17 +683,21 @@ async function executePluralQuery(
 
   // Reverse the order by conditions to get the previous page,
   // then reverse the results back to the original order.
-  const rows = await baseQuery
-    .findMany({
-      where: and(...whereConditions, cursorCondition, ...extraConditions),
-      orderBy: orderByReversed,
-      limit: limit + 2,
-    })
-    .then((rows) => rows.reverse());
+  const [rows, totalCount] = await Promise.all([
+    baseQuery
+      .findMany({
+        where: and(...whereConditions, cursorCondition, ...extraConditions),
+        orderBy: orderByReversed,
+        limit: limit + 2,
+      })
+      .then((rows) => rows.reverse()),
+    totalCountPromise,
+  ]);
 
   if (rows.length === 0) {
     return {
       items: rows,
+      totalCount,
       pageInfo: { hasNextPage, hasPreviousPage, startCursor, endCursor },
     };
   }
@@ -710,6 +728,7 @@ async function executePluralQuery(
 
   return {
     items: rows,
+    totalCount,
     pageInfo: { hasNextPage, hasPreviousPage, startCursor, endCursor },
   };
 }
@@ -940,12 +959,10 @@ export function buildDataLoaderCache({
         async (encodedIds) => {
           const decodedRowFragments = encodedIds.map(decodeRowFragment);
 
+          // The decoded row fragments should be valid `where` objects
+          // which use the `eq` object shorthand for each primary key column.
           const idConditions = decodedRowFragments.map((decodedRowFragment) =>
-            and(
-              ...Object.entries(decodedRowFragment).map(([col, val]) =>
-                eq(table.columns[col]!, val),
-              ),
-            ),
+            and(...buildWhereConditions(decodedRowFragment, table.columns)),
           );
 
           const rows = await baseQuery.findMany({
@@ -976,3 +993,21 @@ function getColumnTsName(column: Column) {
     ([_, c]) => c.name === column.name,
   )![0];
 }
+
+/**
+ * Returns `true` if the query includes a specific field.
+ * Does not consider nested selections; only works one "layer" deep.
+ */
+function selectionIncludesField(
+  info: GraphQLResolveInfo,
+  fieldName: string,
+): boolean {
+  for (const fieldNode of info.fieldNodes) {
+    for (const selection of fieldNode.selectionSet?.selections ?? []) {
+      if (selection.kind === "Field" && selection.name.value === fieldName) {
+        return true;
+      }
+    }
+  }
+  return false;
+}


Test patch:
diff --git a/packages/core/src/graphql/index.test.ts b/packages/core/src/graphql/index.test.ts
index 6da6be61..31cea81c 100644
--- a/packages/core/src/graphql/index.test.ts
+++ b/packages/core/src/graphql/index.test.ts
@@ -577,6 +577,7 @@ test("singular with many relation using filter", async (context) => {
           items {
             id
           }
+          totalCount
         }
       }
     }
@@ -587,6 +588,66 @@ test("singular with many relation using filter", async (context) => {
     person: {
       pets: {
         items: [{ id: "dog2" }],
+        totalCount: 1,
+      },
+    },
+  });
+
+  await cleanup();
+});
+
+test("singular with many relation using order by", async (context) => {
+  const person = onchainTable("person", (t) => ({
+    id: t.text().primaryKey(),
+    name: t.text(),
+  }));
+  const personRelations = relations(person, ({ many }) => ({
+    pets: many(pet),
+  }));
+  const pet = onchainTable("pet", (t) => ({
+    id: t.text().primaryKey(),
+    age: t.integer(),
+    ownerId: t.text(),
+  }));
+  const petRelations = relations(pet, ({ one }) => ({
+    owner: one(person, { fields: [pet.ownerId], references: [person.id] }),
+  }));
+  const schema = { person, personRelations, pet, petRelations };
+
+  const { database, indexingStore, metadataStore, cleanup } =
+    await setupDatabaseServices(context, { schema });
+  const contextValue = buildContextValue(database, metadataStore);
+  const query = (source: string) =>
+    execute({ schema: graphqlSchema, contextValue, document: parse(source) });
+
+  await indexingStore
+    .insert(schema.person)
+    .values({ id: "jake", name: "jake" });
+  await indexingStore.insert(schema.pet).values([
+    { id: "dog1", age: 1, ownerId: "jake" },
+    { id: "dog2", age: 2, ownerId: "jake" },
+    { id: "dog3", age: 3, ownerId: "jake" },
+  ]);
+
+  const graphqlSchema = buildGraphQLSchema(schema);
+
+  const result = await query(`
+    query {
+      person(id: "jake") {
+        pets(orderBy: "age", orderDirection: "desc") {
+          items {
+            id
+          }
+        }
+      }
+    }
+  `);
+
+  expect(result.errors?.[0]?.message).toBeUndefined();
+  expect(result.data).toMatchObject({
+    person: {
+      pets: {
+        items: [{ id: "dog3" }, { id: "dog2" }, { id: "dog1" }],
       },
     },
   });
@@ -1488,6 +1549,7 @@ test("cursor pagination ascending", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
@@ -1508,6 +1570,7 @@ test("cursor pagination ascending", async (context) => {
         startCursor: expect.any(String),
         endCursor: expect.any(String),
       },
+      totalCount: 9,
     },
   });
 
@@ -1527,6 +1590,7 @@ test("cursor pagination ascending", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
@@ -1546,6 +1610,7 @@ test("cursor pagination ascending", async (context) => {
         startCursor: expect.any(String),
         endCursor: expect.any(String),
       },
+      totalCount: 9,
     },
   });
 
@@ -1565,6 +1630,7 @@ test("cursor pagination ascending", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
@@ -1582,6 +1648,7 @@ test("cursor pagination ascending", async (context) => {
         startCursor: expect.any(String),
         endCursor: expect.any(String),
       },
+      totalCount: 9,
     },
   });
 
@@ -1626,6 +1693,7 @@ test("cursor pagination descending", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
@@ -1643,6 +1711,7 @@ test("cursor pagination descending", async (context) => {
         startCursor: expect.any(String),
         endCursor: expect.any(String),
       },
+      totalCount: 5,
     },
   });
 
@@ -1662,6 +1731,7 @@ test("cursor pagination descending", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
@@ -1680,6 +1750,7 @@ test("cursor pagination descending", async (context) => {
         startCursor: expect.any(String),
         endCursor: expect.any(String),
       },
+      totalCount: 5,
     },
   });
 
@@ -1699,6 +1770,7 @@ test("cursor pagination descending", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
@@ -1713,6 +1785,7 @@ test("cursor pagination descending", async (context) => {
         startCursor: expect.any(String),
         endCursor: expect.any(String),
       },
+      totalCount: 5,
     },
   });
 
@@ -1757,6 +1830,7 @@ test("cursor pagination start and end cursors", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
@@ -1778,6 +1852,7 @@ test("cursor pagination start and end cursors", async (context) => {
         hasPreviousPage: false,
         hasNextPage: false,
       },
+      totalCount: 5,
     },
   });
 
@@ -1822,6 +1897,7 @@ test("cursor pagination has previous page", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
@@ -1844,20 +1920,24 @@ test("cursor pagination has previous page", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
 
   expect(result.errors?.[0]?.message).toBeUndefined();
-  // @ts-ignore
-  expect(result.data.pets.items).toHaveLength(0);
-  // @ts-ignore
-  expect(result.data.pets.pageInfo).toMatchObject({
-    startCursor: null,
-    endCursor: null,
-    // Should return true even if the current page is empty
-    hasPreviousPage: true,
-    hasNextPage: false,
+  expect(result.data).toMatchObject({
+    pets: {
+      items: [],
+      pageInfo: {
+        startCursor: null,
+        endCursor: null,
+        // Should return true even if the current page is empty
+        hasPreviousPage: true,
+        hasNextPage: false,
+      },
+      totalCount: 5,
+    },
   });
 
   await cleanup();
@@ -1909,6 +1989,7 @@ test("cursor pagination composite primary key", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
@@ -1928,6 +2009,7 @@ test("cursor pagination composite primary key", async (context) => {
         startCursor: expect.any(String),
         endCursor: expect.any(String),
       },
+      totalCount: 6,
     },
   });
 
@@ -1948,6 +2030,7 @@ test("cursor pagination composite primary key", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
@@ -1965,6 +2048,7 @@ test("cursor pagination composite primary key", async (context) => {
         startCursor: expect.any(String),
         endCursor: expect.any(String),
       },
+      totalCount: 6,
     },
   });
 
@@ -1985,6 +2069,7 @@ test("cursor pagination composite primary key", async (context) => {
           startCursor
           endCursor
         }
+        totalCount
       }
     }
   `);
@@ -2002,6 +2087,7 @@ test("cursor pagination composite primary key", async (context) => {
         startCursor: expect.any(String),
         endCursor: expect.any(String),
       },
+      totalCount: 6,
     },
   });