ulysses•2mo ago

pagination for aggregate

import { TableAggregate } from '@convex-dev/aggregate'
import { components, internal } from '../_generated/api'
import type { DataModel } from '../_generated/dataModel'
import type { QueryCtx } from '../_generated/server'
import { internalMutation } from '../_generated/server'
import { migrations } from '../migrations'

// Fame aggregate - tracks total fame score per user
const fameAggregate = new TableAggregate<{
  Key: number // to_user_id
  DataModel: DataModel
  TableName: 'fame'
}>(components.fameAggregate, {
  sortKey: (doc) => doc.to_user_id,
  sumValue: (doc) => (doc.is_positive ? 1 : -1)
})

export const backfillFame = migrations.define({
  table: 'fame',
  migrateOne: async (ctx, doc) => {
    // Update fameAggregate for each fame document
    await fameAggregate.insertIfDoesNotExist(ctx, doc)
    console.log(
      'backfilled fame for user',
      doc.to_user_id,
      doc.is_positive ? '+1' : '-1'
    )
  }
})

export const clearFameAggregate = internalMutation({
  args: {},
  handler: async (ctx) => {
    await fameAggregate.clear(ctx)
  }
})

// Run this to backfill fame aggregates for existing data
export const runBackfillFame = migrations.runner(
  internal.model.fame.backfillFame
)

export async function getFameTotal(
  ctx: QueryCtx,
  userId: number
): Promise<number> {
  // Use aggregate for O(log n) fame total calculation
  return await fameAggregate.sum(ctx, {
    bounds: {
      lower: { key: userId, inclusive: true },
      upper: { key: userId, inclusive: true }
    }
  })
}

import { TableAggregate } from '@convex-dev/aggregate'
import { components, internal } from '../_generated/api'
import type { DataModel } from '../_generated/dataModel'
import type { QueryCtx } from '../_generated/server'
import { internalMutation } from '../_generated/server'
import { migrations } from '../migrations'

// Fame aggregate - tracks total fame score per user
const fameAggregate = new TableAggregate<{
  Key: number // to_user_id
  DataModel: DataModel
  TableName: 'fame'
}>(components.fameAggregate, {
  sortKey: (doc) => doc.to_user_id,
  sumValue: (doc) => (doc.is_positive ? 1 : -1)
})

export const backfillFame = migrations.define({
  table: 'fame',
  migrateOne: async (ctx, doc) => {
    // Update fameAggregate for each fame document
    await fameAggregate.insertIfDoesNotExist(ctx, doc)
    console.log(
      'backfilled fame for user',
      doc.to_user_id,
      doc.is_positive ? '+1' : '-1'
    )
  }
})

export const clearFameAggregate = internalMutation({
  args: {},
  handler: async (ctx) => {
    await fameAggregate.clear(ctx)
  }
})

// Run this to backfill fame aggregates for existing data
export const runBackfillFame = migrations.runner(
  internal.model.fame.backfillFame
)

export async function getFameTotal(
  ctx: QueryCtx,
  userId: number
): Promise<number> {
  // Use aggregate for O(log n) fame total calculation
  return await fameAggregate.sum(ctx, {
    bounds: {
      lower: { key: userId, inclusive: true },
      upper: { key: userId, inclusive: true }
    }
  })
}

7 Replies

Convex Bot•2mo 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!

ulyssesOP•2mo ago

export async function getFameLeaderboard(
  ctx: QueryCtx,
  args: {
    paginationOpts: { numItems: number; cursor: string | null }
    order?: 'asc' | 'desc'
  }
) {
  const order = args.order || 'desc'

  const paginateOptions: {
    order: 'asc' | 'desc'
    pageSize: number
    cursor?: string
  } = {
    order,
    pageSize: args.paginationOpts.numItems
  }

  if (args.paginationOpts.cursor !== null) {
    paginateOptions.cursor = args.paginationOpts.cursor
  }

  return await fameAggregate.paginate(ctx, paginateOptions)
}

export async function getFameLeaderboard(
  ctx: QueryCtx,
  args: {
    paginationOpts: { numItems: number; cursor: string | null }
    order?: 'asc' | 'desc'
  }
) {
  const order = args.order || 'desc'

  const paginateOptions: {
    order: 'asc' | 'desc'
    pageSize: number
    cursor?: string
  } = {
    order,
    pageSize: args.paginationOpts.numItems
  }

  if (args.paginationOpts.cursor !== null) {
    paginateOptions.cursor = args.paginationOpts.cursor
  }

  return await fameAggregate.paginate(ctx, paginateOptions)
}

When paginating the fame leaderboard, I get duplicate entry errors, is this design incorrect?

Hmza•2mo ago

i'm not sure if the doc.to_user_id is forced unique here or if there can be a duplicate there that can lead to non unique sort keys but here's something to try to make it truly unique. const fameAggregate = new TableAggregate<{ Key: [number, string]; // [to_user_id, fame_id] DataModel: DataModel TableName: 'fame' }>(components.fameAggregate, { sortKey: (doc) => [doc.to_user_id, doc._id], sumValue: (doc) => (doc.is_positive ? 1 : -1) }) that might solve the problem. also make sure to always use the cursor as it is returned.

ulyssesOP•2mo ago

hm there might be multiple inserts of the same doc.to_user_id. Maybe i designed it wrongly for convex

  fame: defineTable({
    id: v.number(),
    from_user_id: v.number(),
    to_user_id: v.number(),
    is_positive: v.boolean(),
    created_at: v.string(),
    updated_at: v.string()
  })
    .index('by_from_user', ['from_user_id'])
    .index('by_to_user', ['to_user_id'])
    .index('by_users', ['from_user_id', 'to_user_id']),

  fame: defineTable({
    id: v.number(),
    from_user_id: v.number(),
    to_user_id: v.number(),
    is_positive: v.boolean(),
    created_at: v.string(),
    updated_at: v.string()
  })
    .index('by_from_user', ['from_user_id'])
    .index('by_to_user', ['to_user_id'])
    .index('by_users', ['from_user_id', 'to_user_id']),

my fames feature is like a "likes" feature for YouTube videos but in my case, the fame is the amount of likes a user has everytime, a user clicks "fame up", then the is_positive field is true. And so there are multiple records of fame ups and down per to_user_id as multiple users can give them an up or down fame

Hmza•2mo ago

@ulysses ok i understand, i see you are using upper and lower bounds to get the user calculation. that'll give you duplicates as user id is stored multiple times when you insert. try the .sum with prefix to calculate totals. something like this: export async function getFameTotal( ctx: QueryCtx, userId: number ): Promise<number> { // Use a prefix query to sum all fame events for a given user return await fameAggregate.sum(ctx, { prefix: [userId] }); }

ulyssesOP•2mo ago

hmm i get a type error for that:

Diagnostics:
1. Argument of type '[{ prefix: number[]; }]' is not assignable to parameter of type 'NamespacedOpts<{ bounds: Bounds<number, Id<"fame">>; }, undefined>'.
     Type '[{ prefix: number[]; }]' is not assignable to type '[{ namespace: undefined; } & { bounds: Bounds<number, Id<"fame">>; }]'.
       Object literal may only specify known properties, and 'prefix' does not exist in type '{ namespace: undefined; } & { bounds: Bounds<number, Id<"fame">>; }'. [2345]

Diagnostics:
1. Argument of type '[{ prefix: number[]; }]' is not assignable to parameter of type 'NamespacedOpts<{ bounds: Bounds<number, Id<"fame">>; }, undefined>'.
     Type '[{ prefix: number[]; }]' is not assignable to type '[{ namespace: undefined; } & { bounds: Bounds<number, Id<"fame">>; }]'.
       Object literal may only specify known properties, and 'prefix' does not exist in type '{ namespace: undefined; } & { bounds: Bounds<number, Id<"fame">>; }'. [2345]

erquhart•2mo ago

Object with prefix has to be assigned to bounds property:

export async function getFameTotal(
  ctx: QueryCtx,
  userId: number
): Promise<number> {
  // Use a prefix query to sum all fame events for a given user
  return await fameAggregate.sum(ctx, {
    bounds: { prefix: [userId] }
  });
}

export async function getFameTotal(
  ctx: QueryCtx,
  userId: number
): Promise<number> {
  // Use a prefix query to sum all fame events for a given user
  return await fameAggregate.sum(ctx, {
    bounds: { prefix: [userId] }
  });
}

pagination for aggregate

Did you find this page helpful?