How to get random recrods from findMany?

[hyochan]

As discussed in Prisma 1 forum, I'd like to know how to find random records in findMany.

The below query should work in postgres.

SELECT *
FROM users
ORDER BY random()
LIMIT 1;

[nikolasburk]

Hey @hyochan, thanks a lot for raising this! I don't think returning items from findMany in a random order is natively supported by Prisma Client yet and I don't think there's another way how you could retrieve a random element with Prisma Client. If you only care about getting a single item though and not really about the randomness, you can use findFirst.

Alternatively, you can of course use the SQL query that you posted above and send it to the DB using Prisma Client's $queryRaw method.

In any case, I think this is a really interesting feature request, would you mind persisting it in a GitHub issue so that our Product and Engineering teams can look into it?

[hyochan]

Thank you for the reply @nikolasburk. However, I am still confused to select random() with pagination.

      return prisma.item.findMany({
        ...relayToPrismaPagination({
          after,
          before,
          first,
          last,
        }),
        where: {
          ...filters,
        },
        orderBy: {id: 'desc'},
      });

Do you have any idea how I can apply the below raw query to the above pagination query?

select * from "Item" limit 20
offset floor(random() * (select count(*) from "Item"));

[hyochan]

@nikolasburk #5894 Submitted

[hlspablo]

Try something like this

        const productsCount = await prisma.product.count();
        const skip = Math.floor(Math.random() * productsCount);
        return await prisma.product.findMany({
            take: 5,
            skip: skip,
            orderBy: {
                sellingCount: 'desc',
            },
        });

[hlspablo]

Assuming: records = [1,2,3,...,100] How can I take: [1,8,9] as a random 3 elements?

As u can see, it's not possible, is just some starting point for creativity

[felinto-dev]

Assuming: records = [1,2,3,...,100] How can I take: [1,8,9] as a random 3 elements?

take 1 element and run this query 3 times

[BenjaminPiette]

Not bad but if by chance skip is almost at the end, you won't get 5 entries as expected.

[igor-ht]

Assuming: records = [1,2,3,...,100] How can I take: [1,8,9] as a random 3 elements?

const everyIdInTable = await prisma.words.findMany({
	select: { id: true },
});
const idArray = everyIdInTable.map((element) => element.id);
const randomIndex = Math.floor(Math.random() * idArray.length);
const randomIdFromTable = idArray[randomIndex];
const randomElementFromTable = await prisma.words.findFirst({
	where: {
		id: randomIdFromTable,
	},
});

[tuon1602]

is this still usable?

[luatnd]

My current attempt:

async pickRandom(count: number): Promise<CognitiveTest[]>{
    const itemCount = await this.prisma.cognitiveTest.count();
    const skip = Math.max(0, Math.floor(Math.random() * itemCount) - count);
    const orderBy = randomPick(['id', 'field1', 'field2']);
    const orderDir = randomPick([SortOrder.asc, SortOrder.desc]);

    return this.prisma.cognitiveTest.findMany({
      take: count,
      skip: skip,
      orderBy: { [orderBy]: orderDir },
    });
  }

[felinto-dev]

hahahaha awesome!

[AroldoGoulart]

@luatnd
Ive do something like this, works fine here:

async handle(req: Request, res: Response) {
        const randomPick = (values: string[]) => {
            const index = Math.floor(Math.random() * values.length);
            return values[index];
        }
        const itemCount = await prismaClient.book.count();

        const randomNumber = (min: number, max: number) => {
            return Math.floor(Math.random() * (max - min + 1)) + min;
        }

        const orderBy = randomPick(['book_id', 'book_name', 'book_uuid', `book_synopsis`, `book_date_updated`, `book_date_created`]);
        const orderDir = randomPick([`asc`, `desc`]);

        let result = await prismaClient.book.findMany({
            orderBy: { [orderBy]: orderDir },
            take: 1,
            skip: randomNumber(0, itemCount - 1),
            select: {
                book_id: true,
                book_name: true,
                book_synopsis: true,
            }
        })

        res.send(result)
  }

[Luke265]

Yet another problem, that could be easily solved by allowing raw sql in Prisma API. See #5560

If raw sql would be allowed in Prisma API, the solution to this problem could look like this:

prisma.users.findMany({
    orderBy: raw`random()`,
    take: 1
});

[sawirricardo]

I wish this are built-in with Prisma

[jgb-solutions]

+1 for this

[jgb-solutions]

I'm still waiting for this.

[danvayn]

me too.

[Maximilian94]

I'm still waiting for this.

[ghost]

I wait the issue to be completed but if you need a temporary solution :

import { Prisma } from '@prisma/client';
import { merge } from 'lodash';

export const getRandomItems = async <T>(
  model: Prisma.ModelName,
  take = 5,
  options: {
    sqlQuery?: string;
    prismaQuery?: any;
  } = {
    sqlQuery: `SELECT id FROM $model`,
  },
): Promise<T[]> => {
  if (!options?.sqlQuery) {
    throw 'SqlQuery not found';
  }

  const ids = (
    await prisma.$queryRawUnsafe<{ id: string }[]>(
      `${options.sqlQuery!.replaceAll(`$model`, model)} ORDER BY random() LIMIT ${take}`,
    )
  )?.map((r) => r.id);

  return prisma[model].findMany(
    merge(
      {
        where: { id: { in: ids } },
      },
      options?.prismaQuery || {},
    ),
  ) as Promise<T[]>;
};

[KevinBatdorf]

To simplify all the above:

results = await prisma.$queryRawUnsafe(
    // DO NOT pass in or accept user input here
    `SELECT * FROM "Post" ORDER BY RANDOM() LIMIT 30;`,
)

[shtse8]

I would try to avoid raw query if possible.

[KevinBatdorf]

@shtse8 i saw this a bit late but can you explain why?

[lilonghe]

Thanks, it's saved me!

const pageSize = 20
let ids: string[] = [];
  
const results = await prisma.$queryRawUnsafe<{id: string}[]>(`SELECT id FROM public."xxx" ORDER BY RANDOM() LIMIT ${pageSize};`)
  if (!results) {
    console.log(results)
    return { list: [] }
  }

ids = results.map(item => item.id)
const res = await prisma.xxx.findMany({
    take: pageSize,
    skip: 0,
    where: {
      id: {
        in: ids
      }
    },
...

[SH20RAJ]

import prisma from "../../../../../prisma";

let Page = async () => {
  let results = await prisma.$queryRawUnsafe(
    `SELECT * FROM Post ORDER BY RAND() LIMIT 30;`, // For MySQL and MariaDB
    // `SELECT * FROM Post ORDER BY RANDOM() LIMIT 30;`, // For PostgreSQL
  )
  return (
    <div>
      {results.map((post) => (
        <div key={post.id}>
          <h2>{post.title}</h2>
          <p>{post.content}</p>
        </div>
      ))}
    </div>
  )
}

export default Page

[zhe]

For MySQL or PlanetScale, a sample code:

  return await prisma.$queryRaw`SELECT * FROM accounts WHERE is_public = 1 ${
    adminId ? Prisma.sql`AND admin_id <> ${adminId}` : Prisma.empty
  } ORDER BY RAND() LIMIT 3`;

[younes-alouani]

+1.

[jsonwulff]

With the new Views feature in Prisma you could create a view with

SELECT *
FROM users
ORDER BY random()

and then create a view model in your Prisma schema that mirrors your user model.

[jgb-solutions]

@jsonwulff hey, care to elaborate on this?

[iway1]

things to keep in mind:

Using ORDER BY random() event with LIMIT will generate a random number for every row in the table
Using skip will require traversing the number of skipped rows

Can be an issue with large tables

[stepandel]

You can pass the seed to make sure the order stays the same for subsequent runs.
Not sure how to do it in prisma but in raw sql it would look like this:

SELECT *, setseed(${seed})::Text
FROM users
ORDER BY random()
TAKE 100
LIMIT 100

[talesmgodois]

async pickRandom(count: number): Promise<CognitiveTest[]>{
    const ids = await prisma.$queryRaw`SELECT id FROM tablle ORDER BY RAND() LIMIT ${count}`;
    return this.prisma.cognitiveTest.findMany({
      ids: {
        in: ids
      }
    });
  }

[felipe-ds-lima]

function getRandomGifts() {
  const allIds = await this.databaseService.gift.findMany({
    select: {
      id: true,
    },
  })
  
  allIds.sort(() => Math.random() - 0.5)
  const randomIds = allIds.slice(0, 20).map((gift) => gift.id)

  return await this.databaseService.gift.findMany({
    where: { id: { in: randomIds } },
  })
}

[tetthys]

Prisma still doesn't support this yet lol

Why should everyone suffer by not providing randomBy?

About 3 years have passed, but nothing has changed.

This isn't even a special feature. It is used by many developers.

I really wonder that the Prisma development team consider some useful tools for web developer.

[SH20RAJ]

To simplify all the above:

results = await prisma.$queryRawUnsafe(
    // DO NOT pass in or accept user input here
    `SELECT * FROM "Post" ORDER BY RANDOM() LIMIT 30;`,
)