ellis.valentiner•4w ago

Offset-based pagination returns NULL

I'm trying to implement offset-based pagination however almost every query returns null. Random access works fine. Getting the total count gives me a number greater than the number of records in the table. I've "repaired" my aggregates. Setting the offset to a high value sometimes returns a result but seems to be a random field from the record.

13 Replies

Convex Bot•4w ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

ellis.valentinerOP•4w ago

convex/convex.config.ts

import { defineApp } from "convex/server";
import aggregate from "@convex-dev/aggregate/convex.config";
import migrations from "@convex-dev/migrations/convex.config";

const app = defineApp();
app.use(aggregate);
app.use(aggregate, { name: "items" });
app.use(aggregate, { name: "pageOfItems" });
app.use(aggregate, { name: "pageOfPopularItems" });
app.use(aggregate, { name: "pageOfAlphabeticalItems" });

app.use(migrations);

export default app;

import { defineApp } from "convex/server";
import aggregate from "@convex-dev/aggregate/convex.config";
import migrations from "@convex-dev/migrations/convex.config";

const app = defineApp();
app.use(aggregate);
app.use(aggregate, { name: "items" });
app.use(aggregate, { name: "pageOfItems" });
app.use(aggregate, { name: "pageOfPopularItems" });
app.use(aggregate, { name: "pageOfAlphabeticalItems" });

app.use(migrations);

export default app;

convex/migrations.ts

import {components, internal} from "./_generated/api";
import {pageOfItems, pageOfPopularItems, pageOfAlphabeticalItems, randomize} from "./shuffle";
import {Migrations} from "@convex-dev/migrations";
import {DataModel} from "./_generated/dataModel";
import {internalMutation} from "./_generated/server";

export const migrations = new Migrations<DataModel>(components.migrations);
export const run = migrations.runner();

export const backfillAggregatesMigration = migrations.define({
  table: "items",
  batchSize: 10,
  parallelize: true,
  migrateOne: async (ctx, doc) => {
    await randomize.insertIfDoesNotExist(ctx, doc);
    await pageOfItems.insertIfDoesNotExist(ctx, doc);
    await pageOfPopularItems.insertIfDoesNotExist(ctx, doc);
    await pageOfAlphabeticalItems.insertIfDoesNotExist(ctx, doc);
    console.log("backfilled", doc.name);
  },
});

export const clearAggregates = internalMutation({
  args: {},
  handler: async (ctx) => {
    await randomize.clear(ctx);
    await pageOfItems.clear(ctx, {namespace: 'A'});
    await pageOfItems.clear(ctx, {namespace: 'B'});
    await pageOfPopularItems.clear(ctx, {namespace: 'A'});
    await pageOfPopularItems.clear(ctx, {namespace: 'B'});
    await pageOfAlphabeticalItems.clear(ctx, {namespace: 'A'});
    await pageOfAlphabeticalItems.clear(ctx, {namespace: 'B'});
  },
});

export const runAggregateBackfill = 
migrations.runner(
  internal.migrations.backfillAggregatesMigration
);

import {components, internal} from "./_generated/api";
import {pageOfItems, pageOfPopularItems, pageOfAlphabeticalItems, randomize} from "./shuffle";
import {Migrations} from "@convex-dev/migrations";
import {DataModel} from "./_generated/dataModel";
import {internalMutation} from "./_generated/server";

export const migrations = new Migrations<DataModel>(components.migrations);
export const run = migrations.runner();

export const backfillAggregatesMigration = migrations.define({
  table: "items",
  batchSize: 10,
  parallelize: true,
  migrateOne: async (ctx, doc) => {
    await randomize.insertIfDoesNotExist(ctx, doc);
    await pageOfItems.insertIfDoesNotExist(ctx, doc);
    await pageOfPopularItems.insertIfDoesNotExist(ctx, doc);
    await pageOfAlphabeticalItems.insertIfDoesNotExist(ctx, doc);
    console.log("backfilled", doc.name);
  },
});

export const clearAggregates = internalMutation({
  args: {},
  handler: async (ctx) => {
    await randomize.clear(ctx);
    await pageOfItems.clear(ctx, {namespace: 'A'});
    await pageOfItems.clear(ctx, {namespace: 'B'});
    await pageOfPopularItems.clear(ctx, {namespace: 'A'});
    await pageOfPopularItems.clear(ctx, {namespace: 'B'});
    await pageOfAlphabeticalItems.clear(ctx, {namespace: 'A'});
    await pageOfAlphabeticalItems.clear(ctx, {namespace: 'B'});
  },
});

export const runAggregateBackfill = 
migrations.runner(
  internal.migrations.backfillAggregatesMigration
);

My query for random access works:

export const getRandomItem = query({
  args: {},
  handler: async (ctx, args) => {
    console.log("Calling get random item with args", args);
    const randomItem = await randomize.random(ctx);
    console.log("Got random item", randomItem);
    if (!randomItem) {
      console.log("No random item found");
      throw new ConvexError("No random item found");
    }
    const item = await ctx.db.get(randomItem.id);
    console.log("Got random item", item);
    return item!;
  },
});

export const getRandomItem = query({
  args: {},
  handler: async (ctx, args) => {
    console.log("Calling get random item with args", args);
    const randomItem = await randomize.random(ctx);
    console.log("Got random item", randomItem);
    if (!randomItem) {
      console.log("No random item found");
      throw new ConvexError("No random item found");
    }
    const item = await ctx.db.get(randomItem.id);
    console.log("Got random item", item);
    return item!;
  },
});

However trying to use offset based pagination does not because the result of pageOfAlphabeticalItems.at is null:

export const getAlphabeticalItem = query({
  args: {
    category: v.union(v.literal("A"), v.literal("B")),
  },
  handler: async (ctx, {category}): Promise<Name | null> => {
    // Authentication check
    const userId = await authenticateUser(ctx);
    if (!userId) return null;
    const namespace = category;
    console.log("getAlphabeticalItem - namespace", namespace);
    const count = await pageOfAlphabeticalItems.count(ctx, {namespace: namespace, bounds: {}});
    console.log("getAlphabeticalItem - count", count);
    const {key} = await pageOfAlphabeticalItems.at(ctx, 0, {namespace: namespace});
    console.log("getAlphabeticalItem - key", key);
    const item = await ctx.db.query("items")
      .withIndex("by_category_name", (q) => q.eq("category", namespace).gte("name", key))
      .first();
    console.log("getAlphabeticalItem - item", item);
    if (item) return item as Item;
    return null;
  }}
);

export const getAlphabeticalItem = query({
  args: {
    category: v.union(v.literal("A"), v.literal("B")),
  },
  handler: async (ctx, {category}): Promise<Name | null> => {
    // Authentication check
    const userId = await authenticateUser(ctx);
    if (!userId) return null;
    const namespace = category;
    console.log("getAlphabeticalItem - namespace", namespace);
    const count = await pageOfAlphabeticalItems.count(ctx, {namespace: namespace, bounds: {}});
    console.log("getAlphabeticalItem - count", count);
    const {key} = await pageOfAlphabeticalItems.at(ctx, 0, {namespace: namespace});
    console.log("getAlphabeticalItem - key", key);
    const item = await ctx.db.query("items")
      .withIndex("by_category_name", (q) => q.eq("category", namespace).gte("name", key))
      .first();
    console.log("getAlphabeticalItem - item", item);
    if (item) return item as Item;
    return null;
  }}
);

lee•4w ago

hmm this code looks to me like it should work. few questions: - can you share the definitions for each aggregate (in particular the sortKey and namespace)? - what exactly is null, the key or the item? - is "Getting the total count gives me a number greater than the number of records in the table" still true after you repair aggregates? - are you using triggers or some other method to keep the aggregate in sync with the table?

ellis.valentinerOP•4w ago

Hi @Lee ! The key is null, which causes item to also be null. Here is my TableAggregate for random access – this does work:

export const randomize = new TableAggregate<{
  Key: null;
  DataModel: DataModel;
  TableName: "items";
}>(components.items, {
  sortKey: () => null
});

export const randomize = new TableAggregate<{
  Key: null;
  DataModel: DataModel;
  TableName: "items";
}>(components.items, {
  sortKey: () => null
});

Here is my TableAggregate for offset based pagination for alphabetical items:

export const pageOfAlphabeticalItems = new TableAggregate<{
  Namespace: string;
  Key: string;
  DataModel: DataModel;
  TableName: "items";
}>(components.items, {
  namespace: (doc) => doc.category,
  sortKey: (doc) => doc.name,
});

export const pageOfAlphabeticalItems = new TableAggregate<{
  Namespace: string;
  Key: string;
  DataModel: DataModel;
  TableName: "items";
}>(components.items, {
  namespace: (doc) => doc.category,
  sortKey: (doc) => doc.name,
});

I've tried repairing the aggregates but the count is still incorrect, which is suspicious to me.

lee•4w ago

🤔 and is the "items" table changing?

ellis.valentinerOP•4w ago

Nope! I just tried examining the result of await pageOfAlphabeticalItems.at(ctx, 0, {namespace: namespace}); rather than destructuring it and it looks like I do get a valid id and sumValue: 0 But looking at the item with the returned id I can see it is definitely not being sorted by name as I'd have expected

lee•4w ago

oh...

export const pageOfAlphabeticalItems = new TableAggregate<...>(components.items, ...);

export const pageOfAlphabeticalItems = new TableAggregate<...>(components.items, ...);

should be

export const pageOfAlphabeticalItems = new TableAggregate<...>(components.pageOfAlphabeticalItems, ...);

export const pageOfAlphabeticalItems = new TableAggregate<...>(components.pageOfAlphabeticalItems, ...);

ellis.valentinerOP•4w ago

ah I see. Does this mean I need to repair my aggregates again too? I think so. I'll give that a try. Aggregates repaired and it looks like its working now, thanks!

lee•4w ago

🥳 i internally raised the question of whether convex should do something to forestall this issue

ellis.valentinerOP•4w ago

Is it possible to specify reverse sort order?

lee•4w ago

You could find an order-reversing encoding. But can you explain why you want to? Most of the aggregate methods can be inverted

ellis.valentinerOP•4w ago

I want to be able to paginate through items in alphabetical order and reverse alphabetical order (or most popular to least popular vs. least popular to most popular)

lee•4w ago

sounds doable aggregate.at(ctx, -1, { namespace }) gets the item at the end, for example and then you can do

const item = await ctx.db.query("items")
      .withIndex("by_category_name", (q) => q.eq("category", namespace).lte("name", key)).order("desc")
      .first();

Offset-based pagination returns NULL

Did you find this page helpful?