Filtering on many-to-many relationship with join table

[pranavbadami]

Hey folks! I have a question related to this discussion here and figured I'd break it out into it's own post.

Basically, I'm using @kiwicopple 's suggestion to create a "join" table to implement a many-to-many relationship. Here's the sample schema in question:

users: id, name
id: 1, name: Bob
id: 2, name: Alice
id: 3, name: John

teams: id, name
id: 1, name: Foo
id: 2, name: Bar

members: id, user_id, team_id
id: 1, user_id: 1, team_id: 1
id: 2, user_id: 1, team_id: 2
id: 3, user_id: 2, team_id: 1

How do I construct a query with the Supabase JS API to query for all users that are members of team 1? Or members of team 1 and team 2?

So far, I've tried something like the following:

const {data, error} = await supaClient.from("users").select("name, teams (id, name)").in("teams.id", [1, 2])

But that returns something like:

[
 {name: "Bob", teams: [{id: 1, name: "Foo"}, {id: 2, name: "Bar"}]
 {name: "Alice", teams: [{id: 1, name: "Foo"}],
 {name: "John", teams: []}
]

Does anyone know how to construct a query such that John wouldn't be included in the results in the example above? I'm asking because I currently have to filter the result list client-side and would ideally like to avoid having to do that.

Thank you in advance!

[steve-chavez]

Hey @pranavbadami,

Does anyone know how to construct a query such that John wouldn't be included in the results in the example above?

Sorry, this is a current limitation: PostgREST/postgrest#1075. Filtering client-side is the simplest workaround for now.

I'll start working on that one soon.

[pranavbadami]

Ahhh ok gotcha, thanks for clarifying that @steve-chavez . I'll continue forward with client-side filtering for the time being, appreciate it!

[atnegrete]

In the meantime, could you create a view or stored procedure? Then you can use rpc with parameter

see https://supabase.io/docs/reference/javascript/rpc

[pranavbadami]

Hey, thanks for the suggestion! I'm using rpcs a lot already and it's a solid alternative. I was asking about this because I'm constructing some queries "dynamically" similar to this discussion and RPCs are quite cumbersome in that use case.

I appreciate the help though!