Filter query by current user

Question

Schema:

How do I filter a query based on the current user in a joined table?

The closest I've come is with using:

query = supabase.from('shows').select('*, user__shows!left (*)')

if ($user) {
    query = query.not('user__shows.watched', 'is', true)
}

I'm pretty sure it's duplicating entries though which is why it doesn't work on the second query.

I also tried creating a view:

create view browse as
SELECT shows.id, title, year, blurb, imdb_rating, imdb_count, seasons, trailer, status, runtime, country, language, episodes, genres, network, user_id, watched, hidden
FROM user_shows
RIGHT JOIN shows
ON user_shows.show = shows.id
GROUP BY shows.id, watched, hidden, user_id

That didn't filter by current user though.

So, any help would be greatly appreciated.

score 1 · Answer 1 · answered Sep 01 '22 at 22:58

Figured it out and posting the answer for any future souls that are also stumped.

The view works

-- create view browse as
-- SELECT shows.id, title, year, blurb, imdb_rating, imdb_count, seasons, trailer, status, runtime, country, language, episodes, genres, network, watched, hidden
-- FROM shows
-- LEFT JOIN user__shows
-- ON user__shows.show = shows.id
-- GROUP BY shows.id, watched, hidden

the caveat being that row level security works at the level of the view's owner so you have to modify it with

-- ALTER VIEW browse OWNER TO authenticated;

and then I just wrapped it up in an if statement

if ($user) {
    query = supabase.from('browse').select('*')
} else {
    query = supabase.from('shows').select('*')
}

Filter query by current user

1 Answers1