rotating query

[mint rivet]

Hi @sharp hamlet. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

---

Yes, it is possible to serve dynamic, time-bounded random samples from a table in Convex, but it requires combining a few patterns.

Random Sampling:
Convex’s Aggregate component can efficiently retrieve a random document from a table. By using a TableAggregate with a sortKey of null, you can randomize access to documents, as the underlying order will be by document ID, which is generally random. Here’s an example:

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

export const getRandomMusicTitle = query({
  args: {
    cacheBuster: v.optional(v.number()),
  },
  handler: async (ctx) => {
    const randomMusic = await randomize.random(ctx);
    if (!randomMusic) return null;
    const doc = await ctx.db.get(randomMusic.id);
    if (!doc) return null;
    return doc.title;
  },
});

This pattern can be adapted to any table, not just music, and can be used to fetch a random document efficiently Efficient COUNT, SUM, MAX with the Aggregate Component.

Time-Bounded Filtering:
To restrict your random sample to a specific time window, you’ll need to filter your table by a time field (e.g., _creationTime). However, the Aggregate component itself does not natively filter by time window. Instead, you can:

1. Use a query to filter documents within your desired time window.
2. Use the Aggregate component to randomize among those filtered documents, or
3. Collect the filtered documents and select a random one in your query handler.

For example, you could first query for documents within the time window, then pick a random one:

export const getRandomRecentMessage = query({
  args: { minTime: v.number(), maxTime: v.number() },
  handler: async (ctx, { minTime, maxTime }) => {
    const messages = await ctx.db
      .query("messages")
      .withIndex("by_creation_time", q =>
        q.gte("_creationTime", minTime).lte("_creationTime", maxTime)
      )
      .collect();
    if (messages.length === 0) return null;
    const randomIndex = Math.floor(Math.random() * messages.length);
    return messages[randomIndex];
  },
});

Note: Convex ensures that Math.random() is deterministic within a query execution, so this is safe to use in queries Restrictions on queries and mutations.

Summary:

• Use the Aggregate component for efficient random access if you don’t need to filter by time.
• For time-bounded random samples, filter by time in your query, then select a random document from the result set.

If you need to do this at scale or with large datasets, consider pre-aggregating or partitioning your data to make the random selection more efficient.

If you need more advanced randomization or offset-based pagination, see the Aggregate component’s documentation for more patterns Aggregate Example Use Cases.

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: