Can this query be written in Supabase?

I'm developing an app for organizing team sports among friends, have it mostly working, but there's a query I need for displaying “open” games available to join (created by me, or by one of my friends). Specifically:

I want to

select

select

1. games organized by me (I am the organizer)
2. games organized by one of my friends (open to friends)
3. games I'm playing in (joined)

I can easily write the query in regular SQL:

select games.* from games where games.organizer_id = session.user.id
or games.organizer_id in (select friends.friend_id from friends where friends.user_id = session.user.id and friends.accepted = true)
or games.id in (select players.game_id from players where players.user_id = session.user.id);

select games.* from games where games.organizer_id = session.user.id
or games.organizer_id in (select friends.friend_id from friends where friends.user_id = session.user.id and friends.accepted = true)
or games.id in (select players.game_id from players where players.user_id = session.user.id);

...But cannot see how to do it using the Supabase API.

Peaking into two foreign tables

games → players

games → players

and

games → friends

games → friends

with where clauses, Is it even possilbe?

If not, maybe I could achieve the above with an RLS policy?! Any tips or suggestions are very appreciated.

Supabase•4y ago•

19 replies

Smardrengr

Can this query be written in Supabase?

select

select

1. games organized by me (I am the organizer)
2. games organized by one of my friends (open to friends)
3. games I'm playing in (joined)

I can easily write the query in regular SQL:

select games.* from games where games.organizer_id = session.user.id
or games.organizer_id in (select friends.friend_id from friends where friends.user_id = session.user.id and friends.accepted = true)
or games.id in (select players.game_id from players where players.user_id = session.user.id);

select games.* from games where games.organizer_id = session.user.id
or games.organizer_id in (select friends.friend_id from friends where friends.user_id = session.user.id and friends.accepted = true)
or games.id in (select players.game_id from players where players.user_id = session.user.id);

...But cannot see how to do it using the Supabase API.

Peaking into two foreign tables

games → players

games → players

and

games → friends

games → friends

with where clauses, Is it even possilbe?

If not, maybe I could achieve the above with an RLS policy?! Any tips or suggestions are very appreciated.

Can this query be written in Supabase?

Similar Threads

Can this query be written in Supabase?

Similar Threads

Similar Threads

Similar Threads