Random row in table

[sk0le]

Hello, so I have this problem to solve.
I need to get random row in table, and let's say that there is column that contains array of emails.
Now what I want is to select random row, and then check if user email is on that list, if it is than just simply search for next random game.
I really don't have any clue how to do this, so please if anyone can help?

[steve-chavez]

Hey @sk0le,

For getting a random row, you can use the TABLESAMPLE SQL clause:

-- first you'll need to enable this extension
create extension TSM_SYSTEM_ROWS;

-- get one random row
select *  from <your_table> tablesample system_rows(1);

The randomness of the above method depends on the amount of rows you have. If you have few rows, you could do:

-- this will be too slow on big tables
select * from <your_table> order by random() LIMIT 1;

You can wrap one of the above methods on a SQL function and then call it through supabase rpc.

create or replace function get_random_game() returns int as $$
  select id  from <your_table> tablesample system_rows(1);
$$ language sql;

const { data, error } = await supabase
  .rpc('get_random_game')

[alvaaz]

Hi, how do you know how many functions you have created?

[sk0le]

Thank you very much for this response.
Now I've been exploring this rpc and I've realised that you can pass some information like this:

const { data, error } = await supabase
  .rpc('games', { username: 'sk0le' })

Now why is this important, it's because I want that this user to don't get 2 same games.
So what I want to do is to pass his username, then get random row and see if this user has visited that row - 'game', if he visited than try another row (I'm talking here about really big database about 10k games let's say), and then return first random row that he didn't visit. I'm thinking to store usernames in same kind of varchar array. I don't know how to do this.
Thanks anyone for help.

[steve-chavez]

I'm thinking to store usernames in same kind of varchar array.

@sk0le Maybe you should define a many-to-many relationship for this, see this previous discussion: #710 (comment)

@alvaaz You can see your public functions on the API docs(<your_project>/api/default).

[sk0le]

I've handled it like this

create function get_random_row (user_email varchar) returns uuid as $$
  select id  from games tablesample system_rows(1) where not user_email=ANY(guessed_by);
$$ language sql;

And calling it from javascript like this

let { data, error } = await supabase
  .rpc('get_random_row', {
    user_email
  })

if (error) console.error(error)
else console.log(data)

[carlocodes]

hi @sk0le, i am new to supabase and need the same solution to what you've posted. my question is how do you configure the sql function? is it written within the js component or in a separate .sql file? sorry if it is a dumb question

[steve-chavez]

@carlocodes It's separate from the js component, usually it's created through the SQL Editor on the Supabase UI.