Aggregate Help

[graceful junco]

I have the following table:

export const profileTags = defineTable({
tag: v.string(),
profile: v.id("profiles"),
})
.index("by_tag", ["tag"])
.index("by_tag_and_profile", ["tag", "profile"]);

I want to be able to get a count of how many profiles have a specific tag, for example I want to be able to query the tag "Halloween" and get a count that 120 profiles have that specific tag. I want to use the aggregate component. This is my table implementation:

import { TableAggregate } from "@convex-dev/aggregate";
import { DataModel } from "../../_generated/dataModel";
import { components } from "../../_generated/api";

export const profileTagsStats = new TableAggregate<{
Namespace: null; // No namespace since we want to compare across all profiles
Key: [string]; // [favoritedCount, profileId] for sorting
DataModel: DataModel;
TableName: "profileTags";
}>(components.aggregateProfileTags, {
namespace: () => null, // Global namespace to compare all profiles
sortKey: (doc) => [doc.tag], // Sort by favoritedCount
sumValue: (doc) => 1 // Sum the favoritedCount values
});

First is this TableAggregate correct for what I need? Also, how would I backfill this?

[leaden hull]

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

---

Your TableAggregate implementation looks mostly correct for counting profiles with specific tags, but I'll suggest a few adjustments and explain how to use it effectively.

TableAggregate Implementation

Your current implementation is close, but I'd recommend a few changes:

export const profileTagsStats = new TableAggregate<{
    Key: string;  // Just use the tag as the key
    DataModel: DataModel;
    TableName: "profileTags";
}>(components.aggregateProfileTags, {
    sortKey: (doc) => doc.tag,  // Sort by tag
    sumValue: () => 1  // Each document counts as 1
});

The changes I made:

1. Simplified the Key type to just string since you're only sorting by tag
2. Removed the unnecessary namespace since you want to count across all profiles
3. Changed the sortKey to directly use doc.tag instead of an array

With this setup, you can count profiles with a specific tag like this:

// Count profiles with "Halloween" tag
const halloweenCount = await profileTagsStats.count(ctx, {
  bounds: {
    lower: { key: "Halloween", inclusive: true },
    upper: { key: "Halloween", inclusive: true }
  }
});

Backfilling the Aggregate

To backfill your aggregate with existing data, you'll need to:

1. Create a backfill function that processes your existing data
2. Run this function from the Convex dashboard

Here's how you can implement the backfill:

export const backfillProfileTagsStats = internalMutation({
  handler: async (ctx) => {
    // Clear existing aggregate data
    await profileTagsStats.clear(ctx);
    
    // Process in batches to avoid hitting the 8MiB limit
    let cursor = null;
    do {
      const { page, continueCursor } = await ctx.db
        .query("profileTags")
        .paginate({ cursor, numItems: 1000 });
      
      for (const doc of page) {
        await profileTagsStats.insert(ctx, doc);
      }
      
      cursor = continueCursor;
      console.log(`Processed batch, continuing: ${!!cursor}`);
    } while (cursor);
  },
});

This approach uses pagination to avoid the 8MiB limit that you might hit when processing large tables, as mentioned in the knowledge sources Discord support thread: I just don't get the 8iMB limit and how to fix it.

Keeping the Aggregate Updated

To ensure your aggregate stays in sync with your table, you should update the aggregate whenever you modify the table. You can do this using Triggers or by ensuring all your mutations that modify the profileTags table also update the aggregate.

For example, with Triggers:

import { Triggers } from "convex-helpers/server/triggers";
import { customCtx, customMutation } from "convex-helpers/server/customFunctions";
import { mutation as mutationRaw } from "./_generated/server";

const triggers = new Triggers<DataModel>();
triggers.register("profileTags", profileTagsStats.trigger());

// Use this mutation instead of the raw one
export const mutation = customMutation(mutationRaw, customCtx(triggers.wrapDB));

This approach is demonstrated in the Adding a leaderboard with the aggregate component example.

Hope this helps with your implementation!

I'm here to answer any follow-up questions you might have about using the Aggregate component with your profile tags.

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