Calculating average rating across a large dataset

[solar laurel]

Hello! I'm currently looking to build a feature that shows the average rating of a show based on entry logged by users. This is my first time approaching something like this Convex. I was wondering if there were any recommended solutions? I was looking into the Aggregate convex component to potential solve my problem. The only issue I've have with that approach is that a user can log an entry without rating it, but the sameValue does not accept undefined.

export const animeStatsByEntry = new TableAggregate<{
  Namespace: Id<'anime'>
  Key: null
  DataModel: DataModel
  TableName: 'anime_entries'
}>(components.aggregateByAnime, {
  namespace: doc => doc.animeId,
  sortKey: _ => null,
  // TODO: This needs to ignore undefined values instead of setting them to 0
  sumValue: doc => doc.rating ?? 0
})

[sonic stream]

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](https://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!

[raven cradle]

might be a bit of a hack, I wonder what @tired stratus will think. 😄 but you could make the namespace something like [Id<'anime'>, boolean] where the boolean is "did rate", aka rating !== undefined. then you only care about partitions where "did rate" is true

[tired stratus]

That works and it probably easiest 🙂 . Another possibility would be to only insert into the aggregate when rating !== undefined. If you're using a trigger to keep the aggregate updated, that may require writing a custom trigger.

[solar laurel]

Sorry for the late reply but thanks for the suggestions! I wasn't able to figure out how to get the [Id<'anime>, boolean] method working so I ended up going with just do a check before inserting into the aggregate (I'm also looking into triggers). I was wondering if this was a good approach overall?

convex/lib/aggregates.ts

export const animeStatsByEntry = new TableAggregate<{
  Namespace: Id<'anime'>
  Key: null
  DataModel: DataModel
  TableName: 'anime_entries'
}>(components.aggregateByAnime, {
  namespace: doc => doc.animeId,
  sortKey: _ => null,
  // biome-ignore lint/style/noNonNullAssertion:
  sumValue: doc => doc.rating!
})

convex/functions/anime.ts

export const getBySlug = query({
  args: { slug: v.string },
  handler: async (ctx, args) => {
    const anime = await ctx.db
      .query('anime')
      .withIndex('by_slug', q => q.eq('slug', args.slug))
      .first()

    if (!anime) return null

    const opts = {
      namespace: anime._id,
      bounds: {
        lower: undefined,
        upper: undefined
      }
    }

    return {
      ...anime,
      rating:
        (await animeStatsByEntry.sum(ctx, opts)) /
        (await animeStatsByEntry.count(ctx, opts))
    }
  }
})

convex/functions/anime_entries.ts

async function insertRatingAggregate(
  ctx: MutationCtx,
  entryId: Id<'anime_entries'>
) {
  const createdEntry = await ctx.db.get(entryId)
  if (!createdEntry) throw new ConvexError('Failed to get created entry')
  await animeStatsByEntry.insert(ctx, createdEntry)
}

export const createEditorEntry = authRLSMutation({
  args: {
    lists: v.array(v.id('lists')),
    entry: v.object(AnimeEntries.withoutSystemFields)
  },
  handler: async (ctx, args) => {
    const { lists, entry } = args
    const entryId = await ctx.db.insert('anime_entries', entry)

    if (entry.rating) await insertRatingAggregate(ctx, entryId)

    return await Promise.all(
      lists.map(list =>
        ctx.db.insert('anime_lists_entries', {
          entryId,
          listId: list
        })
      )
    )
  }
})