Llabe•9mo ago

Hi, one quick question. Is it possible

Hi, one quick question. Is it possible to retrieve a random element from the DB? That it always returns a random one.

1 Reply

hey @Llabe! we don't have built in support for this, but it's possible to implement this with a random field. here's the idea: in your schema, add a randomKey field and define an index on it. I'll use a messages table as an example:

import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  messages: defineTable({
    author: v.string(),
    body: v.string(),
    randomKey: v.number(),
  }).index("randomKey", ["randomKey"]),
});

import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  messages: defineTable({
    author: v.string(),
    body: v.string(),
    randomKey: v.number(),
  }).index("randomKey", ["randomKey"]),
});

then, whenever you insert into it, use Math.random() for randomKey. for example,

export const send = mutation({
  args: { body: v.string(), author: v.string() },
  handler: async (ctx, { body, author }) => {
    const message = { body, author, randomKey: Math.random() };
    await ctx.db.insert("messages", message);
  },
});

export const send = mutation({
  args: { body: v.string(), author: v.string() },
  handler: async (ctx, { body, author }) => {
    const message = { body, author, randomKey: Math.random() };
    await ctx.db.insert("messages", message);
  },
});

then, we'll use the randomKey index to pick a row randomly. we'll pick a random number and find the subsequent row in randomKey order:

async function sampleMessages(db: DatabaseReader) {
  const last = await db
    .query("messages")
    .withIndex("randomKey")
    .order("desc")
    .first();
  if (!last) {
    return null;
  }
  const key = Math.random() * last.randomKey;
  return db
    .query("messages")
    .withIndex("randomKey", (q) => q.gte("randomKey", key))
    .first();
}

async function sampleMessages(db: DatabaseReader) {
  const last = await db
    .query("messages")
    .withIndex("randomKey")
    .order("desc")
    .first();
  if (!last) {
    return null;
  }
  const key = Math.random() * last.randomKey;
  return db
    .query("messages")
    .withIndex("randomKey", (q) => q.gte("randomKey", key))
    .first();
}

so long as you don't delete records in a pattern correlated with randomKey, the values should remain uniformly distributed on [0, 1), and the sample should be uniform. let me know if this works for you! I'd like to actually prove uniformity mathematically but haven't gotten around to it yet 🙂

Hi, one quick question. Is it possible

Did you find this page helpful?