msy•3w ago

Aggregating by userId and creationTime

Hi im using the Aggregate Convex component. I'm having trouble trying to figure out how I might implement these functions. Right now I believe they are aggregating all tasks. I was wondering how i could further filter to specific userIds?

import { TableAggregate } from "@convex-dev/aggregate";
import { v } from "convex/values";
import { components } from "../_generated/api";
import { DataModel } from "../_generated/dataModel";
import { query } from "../_generated/server";
import { getDocumentOrThrow } from "../utils/db";

const durationAggregate = new TableAggregate<{
  Key: number;
  DataModel: DataModel;
  TableName: "tasks";
}>(components.aggregate, {
  sortKey: (doc) => doc._creationTime,
  sumValue: (doc) => doc.duration,
});

export const totalFocusTimeByUser = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, args) => {
    await getDocumentOrThrow(ctx, "users", args.userId);
    const totalFocusTime = await durationAggregate.sum(ctx);
    return totalFocusTime;
  },
});

export const totalFocusTimeByUserForWeek = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, args) => {
    await getDocumentOrThrow(ctx, "users", args.userId);
    const totalFocusTimeByWeek = await durationAggregate.sum(ctx);
    return totalFocusTimeByWeek;
  },
});

import { TableAggregate } from "@convex-dev/aggregate";
import { v } from "convex/values";
import { components } from "../_generated/api";
import { DataModel } from "../_generated/dataModel";
import { query } from "../_generated/server";
import { getDocumentOrThrow } from "../utils/db";

const durationAggregate = new TableAggregate<{
  Key: number;
  DataModel: DataModel;
  TableName: "tasks";
}>(components.aggregate, {
  sortKey: (doc) => doc._creationTime,
  sumValue: (doc) => doc.duration,
});

export const totalFocusTimeByUser = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, args) => {
    await getDocumentOrThrow(ctx, "users", args.userId);
    const totalFocusTime = await durationAggregate.sum(ctx);
    return totalFocusTime;
  },
});

export const totalFocusTimeByUserForWeek = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, args) => {
    await getDocumentOrThrow(ctx, "users", args.userId);
    const totalFocusTimeByWeek = await durationAggregate.sum(ctx);
    return totalFocusTimeByWeek;
  },
});

4 Replies

Convex Bot•3w 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!

Doogibo•2w ago

Hey @msy ! Someone can correct me if I'm wrong, I'm also currently just diving into using aggregates. Definitely recommend reading this article (a few times even) if you haven't already: https://www.convex.dev/components/aggregate, because there are a lot of things you need to understand to a. get the outputs you actually want, and b. not running into performance issues/write conflicts (https://docs.convex.dev/database/advanced/occ) ...also checking out/running their example which also has tests: https://github.com/get-convex/aggregate First, you have to decide on what you really need here. Do you only care about user specific duration, or do you also care about duration information across multiple users/comparisons and ranking between users? 1. If you only care about user specific durations, you can introduce Namespaces, which will improve performance and reduce potential write conflicts (but will not let you aggregate across users). It would look something like this:

export const durationAggregateByUser = new TableAggregate<{
  Namespace: Id<"tasks">; 
  Key: number; 
  DataModel: DataModel;
  TableName: "tasks";
}>(components.taskDuration, { 
  namespace: (doc) => doc.authorId, 
  sortKey: (doc) => doc._creationTime,
  sumValue: (doc) => doc.duration, 
});

export const durationAggregateByUser = new TableAggregate<{
  Namespace: Id<"tasks">; 
  Key: number; 
  DataModel: DataModel;
  TableName: "tasks";
}>(components.taskDuration, { 
  namespace: (doc) => doc.authorId, 
  sortKey: (doc) => doc._creationTime,
  sumValue: (doc) => doc.duration, 
});

OCC and Atomicity | Convex Developer Hub

In Queries, we mentioned that determinism

GitHub

GitHub - get-convex/aggregate: Component for aggregating counts and...

Component for aggregating counts and sums of Convex documents - get-convex/aggregate

Doogibo•2w ago

Which let's you do things like:

export const totalFocusTimeByUser = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, args) => {
    // Optional: Check if user exists
    await getDocumentOrThrow(ctx, "users", args.userId);

    // Use the 'namespace' option to get the sum for the specific user
    const totalFocusTime = await durationAggregateByUser.sum(ctx, {
      namespace: args.userId,
    });
    return totalFocusTime;
  },
});

export const totalFocusTimeByUser = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, args) => {
    // Optional: Check if user exists
    await getDocumentOrThrow(ctx, "users", args.userId);

    // Use the 'namespace' option to get the sum for the specific user
    const totalFocusTime = await durationAggregateByUser.sum(ctx, {
      namespace: args.userId,
    });
    return totalFocusTime;
  },
});

and

export const totalFocusTimeByUserForWeek = query({
  args: {
    userId: v.id("users"),
    weekStartTimestamp: v.number(), // Pass the start timestamp of the week
    weekEndTimestamp: v.number(),   // Pass the end timestamp of the week
  },
  handler: async (ctx, args) => {
    await getDocumentOrThrow(ctx, "users", args.userId);

    // Define the bounds based on _creationTime (the key)
    const bounds = {
        lower: { key: args.weekStartTimestamp, inclusive: true },
        upper: { key: args.weekEndTimestamp, inclusive: false } // exclusive upper bound usually makes sense for time ranges, play around with this
    };

    // Get the sum within the specified namespace and time bounds
    const totalFocusTimeByWeek = await durationAggregateByUser.sum(ctx, {
      namespace: args.userId,
      bounds: bounds,
    });
    return totalFocusTimeByWeek;
  },
});

export const totalFocusTimeByUserForWeek = query({
  args: {
    userId: v.id("users"),
    weekStartTimestamp: v.number(), // Pass the start timestamp of the week
    weekEndTimestamp: v.number(),   // Pass the end timestamp of the week
  },
  handler: async (ctx, args) => {
    await getDocumentOrThrow(ctx, "users", args.userId);

    // Define the bounds based on _creationTime (the key)
    const bounds = {
        lower: { key: args.weekStartTimestamp, inclusive: true },
        upper: { key: args.weekEndTimestamp, inclusive: false } // exclusive upper bound usually makes sense for time ranges, play around with this
    };

    // Get the sum within the specified namespace and time bounds
    const totalFocusTimeByWeek = await durationAggregateByUser.sum(ctx, {
      namespace: args.userId,
      bounds: bounds,
    });
    return totalFocusTimeByWeek;
  },
});

2. If you need to aggregate across users, etc. get rid of the Namespace and add a sortKey:

export const durationAggregateByAuthorAndTime = new TableAggregate<{
  Key: [Id<"users">, number]; 
  DataModel: DataModel;
  TableName: "tasks";
}>(components.taskDuration, { 
  sortKey: (doc) => [doc.authorId, doc._creationTime], 
  sumValue: (doc) => doc.duration, 
});

export const durationAggregateByAuthorAndTime = new TableAggregate<{
  Key: [Id<"users">, number]; 
  DataModel: DataModel;
  TableName: "tasks";
}>(components.taskDuration, { 
  sortKey: (doc) => [doc.authorId, doc._creationTime], 
  sumValue: (doc) => doc.duration, 
});

You can do things like:

// Same as before, but less efficient for this case than namespacing
export const totalFocusTimeByUserWithKey = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, args) => {
    const totalFocusTime = await durationAggregateByAuthorAndTime.sum(ctx, {
      // Use 'prefix' to bound the query to keys starting with the userId
      prefix: [args.userId],
    });
    return totalFocusTime;
  },
});

// Same as before, but less efficient for this case than namespacing
export const totalFocusTimeByUserWithKey = query({
  args: {
    userId: v.id("users"),
  },
  handler: async (ctx, args) => {
    const totalFocusTime = await durationAggregateByAuthorAndTime.sum(ctx, {
      // Use 'prefix' to bound the query to keys starting with the userId
      prefix: [args.userId],
    });
    return totalFocusTime;
  },
});

and

// Another example: Get the total duration across ALL users (system-wide)
export const totalSystemFocusTime = query({
  args: {},
  handler: async (ctx) => {
    // Calling .sum() without bounds/prefix sums across the entire aggregate
    const totalDuration = await durationAggregateByAuthorAndTime.sum(ctx);
    return totalDuration;
  },
});

// Another example: Get the total duration across ALL users (system-wide)
export const totalSystemFocusTime = query({
  args: {},
  handler: async (ctx) => {
    // Calling .sum() without bounds/prefix sums across the entire aggregate
    const totalDuration = await durationAggregateByAuthorAndTime.sum(ctx);
    return totalDuration;
  },
});

We're not utilizing time ranges in either of these, so technically you don't even need the _creationTime key. If you wanted to figure out the highest duration in a week or something, you'd just add duration to your sortKey and you'd gain the ability to do this. And so on. If you widen your keys (adding more fields to them), it gives you additional flexibility, but you have to be extra diligent about bounding your queries in mutations. It also increases the potential for conflicts when writing to your aggregate. To reduce wide keys, you need more aggregates, which can mean writing to multiple aggregates in response to "task" mutations. So you have to strike a balance between fewer aggregates, wider keys, and more aggregates with more specific keys. This is something I'm struggling with in my own project. The article suggests always putting bounds around your reads, especially when a read is inside of a mutation. When you do this, your aggregate query becomes a part of the mutation's read set, and the "wider" the bounds of your query, the greater your risk of conflicts is. Lastly, I don't see that you're writing to your aggregate. You may just have left that part out, but you have to ensure you do this to keep them in sync. The article suggests 3 approaches for this.

msyOP•2w ago

Hey, I’m currently sick at the moment but really appreciate the in depth answer!! Will dig into it once I’m able to. Thanks so much again

Aggregating by userId and creationTime

Did you find this page helpful?