Get random records from findMany() and also with pagination

[hyochan]

Problem

Comming from #5886.

As discussed in Prisma 1 forum, looks like there is no way to find random records with findMany. Indeed, this is such a challenge to use with pagination.

Suggested solution

Providing a random function from prisma would be an easy solution for users therefore we can expect results with orderBy: random(). Although this results in a bad performance in pure sql, I think Prisma can do better internally without just exposing the random inside the sql.

Alternatives

If the below pagination query and raw query could aggregate, it would scope down many limitations.

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

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

[DraftProducts]

Is this feature planned?
There is still no way today to retrieve a row random in the database with Prisma.

[ryancwalsh]

I've been checking back on this issue and these docs since June 2020 or earlier, and I see that others have wanted this functionality since Feb 2017 or earlier.

@schickling I'm curious whether you think anyone will work on this soon?

Also, I propose an optional seed: number argument (in addition to the first: number argument proposed here).

Sometimes it's helpful to let the client generate and store a seed (such as from a random number generator) that it can then pass to this backend function, which will then return the results sorted in a way that will always be the same (given that the full array of results hasn't changed in the meantime and the same seed was provided).

See the following example of some shuffling code I've used (inspired by Mike Bostock's implementation of the Fisher–Yates algorithm):

/**
 *
 * @param {array} array
 * @param {number} seed
 * @returns {array}
 */
export function shuffle(array: any, seed: number) {
  // https://stackoverflow.com/a/53758827/

  const result = [...array];
  let a = result.length;
  let b;
  let elementToMove;
  let currentSeed = seed;

  // While there remain elements to shuffle…
  while (a) {
    // Pick a remaining element…
    a -= 1;
    b = Math.floor(random(currentSeed) * a);

    // And swap it with the current element.
    elementToMove = result[a];
    result[a] = result[b];
    result[b] = elementToMove;
    currentSeed += 1;
  }

  return result;
}

[deemeetree]

Yes, we absolutely need this feature. Please, consider adding it into the next upgrades.

[EliasTouil]

I will find a workaround but I am also very interested in this feature.

[bhupesh98]

I required this feature too, please tag me whenever there's update to this issue. Absence of advanced SQL functions is making me dismiss the usage of prisma in further projects.

There must be a totally raw query option which can bypass prisma engine and send query directly to db without any syntax checking

[GauravBR]

Is there any way to get only one random value after adding initial filter like where:{ }?