Nishil Faldu•4w ago

Table Aggregrate Component Question

likes: defineTable({
    userId: v.id('users'),
    parentId: v.union(
      v.id('posts'),
      v.id('discussions'),
      v.id('events'),
      v.id('comments')
    ),
    parentType: v.union(
      v.literal('posts'),
      v.literal('discussions'),
      v.literal('events'),
      v.literal('comments')
    ),
    
    universityId: v.id('universities'),
  })
    .index('byParentIdAndUserId', ['parentId', 'userId'])
    .index('byParentId', ['parentId']),

likes: defineTable({
    userId: v.id('users'),
    parentId: v.union(
      v.id('posts'),
      v.id('discussions'),
      v.id('events'),
      v.id('comments')
    ),
    parentType: v.union(
      v.literal('posts'),
      v.literal('discussions'),
      v.literal('events'),
      v.literal('comments')
    ),
    
    universityId: v.id('universities'),
  })
    .index('byParentIdAndUserId', ['parentId', 'userId'])
    .index('byParentId', ['parentId']),

How would I generally write the TableAggregrate component for this? I am very confused and I can't seem to understand or figure it out. My goal is to count number of likes from an input of university id

8 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!

lee•4w ago

If all you need to do is count likes for each university id, i would use the sharded-counter component instead Can you share the code for what you have so far?

Nishil FalduOP•4w ago

I saw the sharded counter but I didn't see a way to calculate all likes for a particular university id - i didn't see place to mention id anywhere. I have the schema (and other functions not related to what i am trying to do now) and I can share that if you like... oh nvm i am blind

Nishil FalduOP•4w ago

i found this now

Nishil FalduOP•4w ago

oh i also remembered now why i wanted to use aggregate - i also wanted to find out number of likes over the past week and i saw on the docs page that i could do it with aggregate

lee•4w ago

Oh yeah then you want aggregate Have you tried installing and configuring the component? How far did you get

Nishil FalduOP•3w ago

yep installing and configuring is done. I was trying to write the TableAggregate now and got confused with the type of parentId - below is my first attempt

const likesByParentAggregate = new TableAggregate<{
  Namespace: Id<"universities">;
  Key: [string, Id<"posts" | "discussions" | "events" | "comments">];
  DataModel: DataModel;
  TableName: "likes";
}>(components.likesAggregate, {
  namespace: (doc) => doc.universityId,
  sortKey: (doc) => [doc.parentType, doc.parentId],
});

const likesByParentAggregate = new TableAggregate<{
  Namespace: Id<"universities">;
  Key: [string, Id<"posts" | "discussions" | "events" | "comments">];
  DataModel: DataModel;
  TableName: "likes";
}>(components.likesAggregate, {
  namespace: (doc) => doc.universityId,
  sortKey: (doc) => [doc.parentType, doc.parentId],
});

const likesAggregate = new TableAggregate<{
  Namespace: Id<"universities">;  // Group by university ID
  Key: number;                // Using creation time for time-based queries
  DataModel: DataModel;
  TableName: "likes";
}>(components.likesAggregate, {
  namespace: (doc) => doc.universityId,
  sortKey: (doc) => doc._creationTime, // Track likes over time
  sumValue: () => 1, // Each like counts as 1
});

const likesAggregate = new TableAggregate<{
  Namespace: Id<"universities">;  // Group by university ID
  Key: number;                // Using creation time for time-based queries
  DataModel: DataModel;
  TableName: "likes";
}>(components.likesAggregate, {
  namespace: (doc) => doc.universityId,
  sortKey: (doc) => doc._creationTime, // Track likes over time
  sumValue: () => 1, // Each like counts as 1
});

this is probably best suited for my case. can someone teach me how to add bounds to this query? for eg: how do i get total likes from a university from the past 7 days?

likesAggregate.count(ctx, {
        namespace: <university id goes here>,
        bounds: {
          lower: {key: Date.now() - 1000 * 60 * 60 * 24 * 365, inclusive: true},
          upper: {key: Date.now(), inclusive: false},
        }
      })

likesAggregate.count(ctx, {
        namespace: <university id goes here>,
        bounds: {
          lower: {key: Date.now() - 1000 * 60 * 60 * 24 * 365, inclusive: true},
          upper: {key: Date.now(), inclusive: false},
        }
      })

does this look good?

lee•3w ago

Looks good to me If you're just using count, you don't need to set sumValue Otherwise looks like it should work 🙂

Table Aggregrate Component Question

Did you find this page helpful?