raul.sp•3mo ago

getting random "n" entries from a table with db query

export const getQuestionsWithSession = mutation({
  args: {},
  handler: async (ctx, _) => {
    const random_questions = await ctx.db.query("session_question").take(5);
    // const id = await ctx.db.insert("session_question", { text: "" });
  },
});

Lets say this " const random_questions = await ctx.db.query("session_question").take(5); " is where i want to get random 5 entries from table "session_question" . I am sure there are other hacky ways using sort, slice. Is there any convex native way of getting random entries from a table ?

4 Replies

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

Hmza•3mo ago

there's no native way todo this in convex but can be achieved doing something like this

export default query(async (ctx) => {
    const earliest = await ctx.db
      .query("table")
      .order("asc")
      .first();
    const latest = await ctx.db
      .query("table")
      .order("desc")
      .first();
    if (!earliest || !latest) {
      return [];
    }
    const earliestTime = earliest._creationTime;
    const latestTime = latest._creationTime;
    const randomTime = earliestTime + Math.random() * (latestTime - earliestTime);
    const randomDocs = await ctx.db
      .query("table")
      .withIndex("by_creation_time", (q) => q.gte("_creationTime", randomTime))
      .take(10);
    return randomDocs;
})

export default query(async (ctx) => {
    const earliest = await ctx.db
      .query("table")
      .order("asc")
      .first();
    const latest = await ctx.db
      .query("table")
      .order("desc")
      .first();
    if (!earliest || !latest) {
      return [];
    }
    const earliestTime = earliest._creationTime;
    const latestTime = latest._creationTime;
    const randomTime = earliestTime + Math.random() * (latestTime - earliestTime);
    const randomDocs = await ctx.db
      .query("table")
      .withIndex("by_creation_time", (q) => q.gte("_creationTime", randomTime))
      .take(10);
    return randomDocs;
})

also this thread is where its discusssed before, another way: https://discord.com/channels/1019350475847499849/1215250213623562250/1215289564856320071

raul.spOP•3mo ago

@Hmza oh, ok. thanks

lee•3mo ago

This can be done with the Aggregate component, which you can get access to https://discord.com/channels/1019350475847499849/1019350478817079338/1290838298595688458

getting random "n" entries from a table with db query

Did you find this page helpful?