Get a random row | Convex Community | Page 1

prisma vigil Apr 16, 2025, 9:38 AM

#

Is there an efficient way to get (pseudo) random row from the database?

sturdy pythonBOT Apr 16, 2025, 9:38 AM

#

Hi @prisma vigil. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

#

Yes, Convex provides an efficient way to get random rows from your database without having to fetch the entire table. You can use the Aggregate component, which allows you to access random documents in O(log n) time.

Using the Aggregate Component

The Aggregate component provides a random() function that lets you efficiently retrieve random documents from your database. This is much more efficient than fetching all documents and then selecting randomly on the client side.

Components > Aggregate specifically mentions random access as one of its use cases:

Random access: Look up a random song in a playlist, as the next song to play.

Implementation Example

Here's how you can implement random document access:

First, install and set up the Aggregate component in your Convex project
Configure your convex.config.ts file to use the aggregate component
Create a TableAggregate for your table
Use the random() function to get a random document

Here's a basic example of how to query for a random document:

export const getRandomItem = query({
  args: {},
  handler: async (ctx, args) => {
    const randomItem = await randomize.random(ctx);
    if (!randomItem) {
      throw new ConvexError("No random item found");
    }
    const item = await ctx.db.get(randomItem.id);
    return item;
  },
});

Handling Determinism

It's worth noting that Convex functions (queries and mutations) are designed to be deterministic. This means that Convex provides a "seeded" pseudo-random number generator at Math.random() to ensure determinism in your functions. If you need consistent randomness across function calls, you can provide a seed value as an argument to your function.

#

Functions > Runtimes explains:

Convex provides a "seeded" strong pseudo-random number generator at Math.random() so that it can guarantee the determinism of your function. The random number generator's seed is an implicit parameter to your function.

For Testing Purposes

If you're looking to generate random data for testing, you might also consider using a library like Faker.js as described in Stack > Generating Fake Data, which provides helpful functions to generate realistic values in large quantities.

Hope this helps with your random document access needs!

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:

#

@prisma vigil Kapa.ai is still learning and improving, please let me know how I did by reacting below.

prisma vigil Apr 16, 2025, 9:43 AM

#

@sturdy python
how do i create a table aggregate for this table

  game_songs: defineTable({
    title: v.string(),
    era: v.string(),
    elo: v.number(),
  }),

sturdy pythonBOT Apr 16, 2025, 9:43 AM

#

Let me check the knowledge base for you again. One moment! :books:

#

I'll show you how to create a TableAggregate for your game_songs table in Convex. This will allow you to efficiently perform operations like counting, summing, or accessing random songs.

Step 1: Install the Aggregate Component

First, install the component:

npm install @convex-dev/aggregate

Step 2: Configure Your Project

Create or update your convex/convex.config.ts file:

// convex/convex.config.ts
import { defineApp } from "convex/server";
import aggregate from "@convex-dev/aggregate/convex.config";

const app = defineApp();
app.use(aggregate, { name: "gameSongs" }); // Give it a specific name

export default app;

Step 3: Create the TableAggregate

Now, create your TableAggregate in a file like convex/songs.ts:

import { TableAggregate } from "@convex-dev/aggregate";
import { components } from "./_generated/api";
import { DataModel } from "./_generated/dataModel";

// For random access or total count
export const randomize = new TableAggregate<{
  Key: null;
  DataModel: DataModel;
  TableName: "game_songs";
}>(components.gameSongs, {
  sortKey: () => null,
});

// For sorting by elo rating
export const songsByElo = new TableAggregate<{
  Key: number;
  DataModel: DataModel;
  TableName: "game_songs";
}>(components.gameSongs, {
  sortKey: (doc) => doc.elo,
});

// For grouping by era
export const songsByEra = new TableAggregate<{
  Namespace: string;
  Key: number;
  DataModel: DataModel;
  TableName: "game_songs";
}>(components.gameSongs, {
  namespace: (doc) => doc.era,
  sortKey: (doc) => doc.elo,
});

Step 4: Set Up Triggers to Keep the Aggregate in Sync

To ensure your aggregate stays in sync with your table, set up triggers:

#

import { Triggers } from "convex-helpers/server/triggers";
import { customCtx, customMutation } from "convex-helpers/server/customFunctions";
import { mutation as mutationRaw } from "./_generated/server";

const triggers = new Triggers<DataModel>();
triggers.register("game_songs", randomize.trigger());
triggers.register("game_songs", songsByElo.trigger());
triggers.register("game_songs", songsByEra.trigger());

// Use these instead of the raw versions
export const mutation = customMutation(mutationRaw, customCtx(triggers.wrapDB));

Step 5: Backfill Existing Data

If you already have data in your table, you'll need to backfill the aggregate:

import { internalMutation } from "./_generated/server";
import { Migrations } from "@convex-dev/migrations";

export const migrations = new Migrations<DataModel>(components.migrations);

export const backfillSongsMigration = migrations.define({
  table: "game_songs",
  batchSize: 100,
  parallelize: true,
  migrateOne: async (ctx, doc) => {
    await randomize.insertIfDoesNotExist(ctx, doc);
    await songsByElo.insertIfDoesNotExist(ctx, doc);
    await songsByEra.insertIfDoesNotExist(ctx, doc);
  },
});

export const runSongsBackfill = migrations.runner(backfillSongsMigration);

Example Usage

Now you can use your aggregates in queries:

// Get a random song
export const getRandomSong = query({
  args: {},
  handler: async (ctx) => {
    const randomSong = await randomize.random(ctx);
    if (!randomSong) return null;
    return await ctx.db.get(randomSong.id);
  },
});

#

// Get top 10 songs by elo
export const getTopSongs = query({
args: {},
handler: async (ctx) => {
const count = await songsByElo.count(ctx);
const topIndices = Array.from({length: 10}, (_, i) => count - i - 1);
const songs = await Promise.all(
topIndices.map(async (i) => {
const song = await songsByElo.at(ctx, i);
return await ctx.db.get(song.id);
})
);
return songs.filter(Boolean);
},
});

// Get songs from a specific era
export const getSongsByEra = query({
args: { era: v.string() },
handler: async (ctx, { era }) => {
const count = await songsByEra.count(ctx, { namespace: era });
const indices = Array.from({length: count}, (_, i) => i);
const songs = await Promise.all(
indices.map(async (i) => {
const song = await songsByEra.at(ctx, i, { namespace: era });
return await ctx.db.get(song.id);
})
);
return songs.filter(Boolean);
},
});


This setup gives you efficient ways to access your game songs randomly, by elo rating, or grouped by era. The aggregate component handles the heavy lifting of maintaining these indexes for you.

[Components > Aggregate](<https://www.convex.dev/components/aggregate>) provides more detailed information about additional operations you can perform with your TableAggregate.
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------

#Get a random row