entropy
entropy•3w ago

Calculating average rating across a large dataset

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
})
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
})
5 Replies
Convex Bot
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!
jamwt
jamwt•3w ago
might be a bit of a hack, I wonder what @lee 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
lee
lee•3w ago
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.
entropy
entropyOP•3w ago
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!
})
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))
    }
  }
})
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
        })
      )
    )
  }
})
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
        })
      )
    )
  }
})
lee
lee•3w ago
Looks good to me! Nice work

Did you find this page helpful?