Query random item(s) from database (Supabase)

Ask us anything
1

Hi I am building a quiz app, I wonder how to query random single question from my questions table in supabase. For example I want to query a question 1 by 1 each page.

2

I figured I need to make another query to find the length first, which returns all the id, get the length of the collections, and used it for the other query. I guess this will cause some leakage of data. If you found better solution please help

3

Not only a leakage of data but also a waterfall of requests that will slow down the load of your quiz (wait for first query and then wait for the second query with the data you need).

With supabase you can create database functions that allow you to do get a random row with only one request.
Have a look at the documentation.

In short:

  • create and run this SQL snippet:
create or replace function get_random_quiz()
returns setof quiz
language sql
as $$
   select * from quiz 
   order by random()
   limit 1;
$$;
  • call the supabase REST API in your workflow with a GET request to the url https://<project_ref>.supabase.co/rest/v1/rpc/get_random_quiz. Be sure to switch on the option to make the request with the server and to add two header to the request:
    • apikey: <SUPABASE_ANON_KEY>
    • Authorization: Bearer <SUPABASE_ANON_KEY>

This specific way of getting a random row (order by random()) is good only for small tables. If you are going to work with big tables you can modify the query to randomly select in a more performant way.

3 Likes
4

Thank you so much @dorilama :pray: :pray: :pray:, I will try it and update the outcome

1 Like
5

@dorilama I came across this video after watching @Quentin live on supabase views. So basically I generated a new view that contains random order of my quiz. Really quick and easy solution. Thank you!

Also @Quentin the video was very helpful. I am just getting the hang of backend, I think we need more on those since us no-coders never touched on API before, so we don’t know what’s the best practices for databases and backend.

1 Like
6

This is also another good way to get the same result.
Now you can choose between two solutions :slight_smile:

1 Like
7

keep in mind that with the solution in the video you is using order by random() as well, so you will hit the same performance problem if your table is big.

2 Likes
8

Hey dorilama when is a table considered to big for this method of getting a random record? And what would be a better way when you have big table? Thanks in advance.

9

there isn’t a single answer for this. as you can read with a google search the problem depends on the specific details of your case (table schema, indexes etc)

what’s the size of your table? you can test multiple methods and compare performances with your data.