https://www.prisma.io/ logo
Join Slack
Powered by
Is it possible in Prisma to get random records? I ...
# orm-help
a
Is it possible in Prisma to get random records? I want to get 10 random posts from my Post table. with 
prisma.posts.count();
I can get the Table size, and with 
Math.random()
I generated 10 random int values. How can I now query my database and get 10 random posts?
⬆️ 1
I use UUID as id thats why I can’t just get a record where the id == the random value. If the Posts table is 300 entries long and my random Int is 120, I want to get the post which is at index 120
d
Get a list of record..then randomly pick one item from that list
r
I guess that works for smaller tables (Aaron said 300 entries in his example), but that can get expensive in a very large table
I was trying to come up with a way to randomly reduce that search space, here's an idea: as far as I know, UUIDs are composed of hex characters (
0123456789abcdef
), if you were to do a "starts with random hex character" query on something like
Copy code
WHERE "id" LIKE %{RANDOM_CHARACTER}
it should atleast reduce the returned rows to 1/16th the size of an "select all" query. I'm also unsure of how efficient these pattern matching queries are, and what the tradeoffs would be, but hey, it's just an idea 🤷
o
It's something I've been waiting for Prisma to support for a long time now.
⬆️ 1
j
prisma.$queryRaw('SELECT * FROM posts ORDER BY RAND() LIMIT 10')
👍 2
I believe that should have a fairly even distribution.
a
@Jason Kleinberg that was very helpful, thank you very much. Never worked with raw querys before but works like a charm.
j
They’re essential when working with Prisma. Prisma does a lot of things well, but there are some queries that are just impossible without getting back down the to the SQL.
n
Here’s the Feature Request for getting random records: #5894 You might want to have a look at it.
58 Views
Open in Slack
PreviousNext
Powered by