2

Suppose I have several millions of statements in my PostgreSQL database and I want to get only 10000 of them. But not the first 10000, rather, a random selection of 10000 (it would be best if I could also choose the logic, e.g. select every 4th statement).

How could I do this using Prisma, or — if it's not possible using Prisma — using a good old PostgreSQL request?

For now, I'm using this code to limit the number of results I'm getting:

const statements = await this.prisma.statement.findMany({
      where: {
        OR: conditions,
      },
      orderBy: {
        createdAt: 'asc',
      },
      take: 10000,
    });

This will use the conditions I have, then order them in ascending order, and "take" or limit the first 10000 results.

What could I use in place of the "take" or what request I could make directly in PostgreSQL to randomly sample my DB for records?

Aerodynamika
  • 7,883
  • 16
  • 78
  • 137
  • 1
    Please have a look at `[this](https://stackoverflow.com/questions/8674718/best-way-to-select-random-rows-postgresql)` post. – user15411679 Mar 17 '22 at 17:42

1 Answers1

1

Prisma doesn't natively support fetching random data as of now.

There is a Feature Request that discusses the exact same scenario as you need.

The alternative could be to use queryRaw for raw database access and use PostgreSQL's random function as described in the above mentioned Feature Request.

Nurul Sundarani
  • 5,550
  • 3
  • 23
  • 52
  • Thank you, @Nurul. Could you please tell me how I use the `random` function to retrieve the random 10000 from all the available records? This was not clear to me from the comment above. Thanks! – Aerodynamika Mar 21 '22 at 07:39