Filtering Users by Team Membership in Supabase Query

Hello everyone,

I'm working on a project using Supabase and I've encountered an issue with a specific query. I'm trying to filter users based on their team memberships. My database has three tables: users, teams, and members. The members table is a junction table for the many-to-many relationship between users and teams.

Here are the table definitions:

create table users (
  "id" serial primary key,
  "name" text
);

create table teams (
  "id" serial primary key,
  "team_name" text
);

create table members (
  "user_id" int references users,
  "team_id" int references teams,
  primary key (user_id, team_id)
);

create table users (
  "id" serial primary key,
  "name" text
);

create table teams (
  "id" serial primary key,
  "team_name" text
);

create table members (
  "user_id" int references users,
  "team_id" int references teams,
  primary key (user_id, team_id)
);

My goal is to write a query that returns only the users who are part of specific teams (e.g., "Blue" and "Yellow"). However, I'm facing challenges in filtering out users who are not in these teams.

I tried the following queryies:

this.supabase.client
  .from("users")
  .select("id, name, members!inner(teams(team_name))")
  .in("members.teams.team_name", ["Blue", "Yellow"])
  .then((res) => {});

this.supabase.client
  .from("users")
  .select("id, name, members!inner(teams(team_name))")
  .in("members.teams.team_name", ["Blue", "Yellow"])
  .then((res) => {});

this.supabase.client.from("users").select("*, teams(team_name)").in(
  "teams.team_name",
  ["Blue", "Yellow"],
).not("teams.team_name", "eq", [])
  .then(
    (res) => {},
  );

this.supabase.client.from("users").select("*, teams(team_name)").in(
  "teams.team_name",
  ["Blue", "Yellow"],
).not("teams.team_name", "eq", [])
  .then(
    (res) => {},
  );

this.supabase.client
    .from("users").select("*, teams(team_name)").in(
      "teams.team_name",
      ["Blue"],
    ).then(
      (res) => {},
    );

this.supabase.client
    .from("users").select("*, teams(team_name)").in(
      "teams.team_name",
      ["Blue"],
    ).then(
      (res) => {},
    );

But this query doesn't work as expected. It seems to be including users who are not part of the specified teams. For instance, I'm getting a response that includes a user who is not in any team:

[
  {"id":1,"name":"Anne","members":[{"teams": {"team_name": "Blue"}}]},
  {"id":2,"name":"Bob","members":[{"teams": {"team_name": "Yellow"}}]},
  {"id":3,"name":"Alice","members":[{"teams": null}]}
]

[
  {"id":1,"name":"Anne","members":[{"teams": {"team_name": "Blue"}}]},
  {"id":2,"name":"Bob","members":[{"teams": {"team_name": "Yellow"}}]},
  {"id":3,"name":"Alice","members":[{"teams": null}]}
]

[
  {"id":1,"name":"Anne","members":["Blue"]},
  {"id":2,"name":"Bob","members":["Yellow"]},
  {"id":3,"name":"Alice","members":[]}
]

[
  {"id":1,"name":"Anne","members":["Blue"]},
  {"id":2,"name":"Bob","members":["Yellow"]},
  {"id":3,"name":"Alice","members":[]}
]

Any help is appreciated! Thank you in advance!

Filtering Users by Team Membership in Supabase Query

create table users (
  "id" serial primary key,
  "name" text
);

create table teams (
  "id" serial primary key,
  "team_name" text
);

create table members (
  "user_id" int references users,
  "team_id" int references teams,
  primary key (user_id, team_id)
);

create table users (
  "id" serial primary key,
  "name" text
);

create table teams (
  "id" serial primary key,
  "team_name" text
);

create table members (
  "user_id" int references users,
  "team_id" int references teams,
  primary key (user_id, team_id)
);

this.supabase.client
  .from("users")
  .select("id, name, members!inner(teams(team_name))")
  .in("members.teams.team_name", ["Blue", "Yellow"])
  .then((res) => {});

this.supabase.client
  .from("users")
  .select("id, name, members!inner(teams(team_name))")
  .in("members.teams.team_name", ["Blue", "Yellow"])
  .then((res) => {});

this.supabase.client.from("users").select("*, teams(team_name)").in(
  "teams.team_name",
  ["Blue", "Yellow"],
).not("teams.team_name", "eq", [])
  .then(
    (res) => {},
  );

this.supabase.client.from("users").select("*, teams(team_name)").in(
  "teams.team_name",
  ["Blue", "Yellow"],
).not("teams.team_name", "eq", [])
  .then(
    (res) => {},
  );

this.supabase.client
    .from("users").select("*, teams(team_name)").in(
      "teams.team_name",
      ["Blue"],
    ).then(
      (res) => {},
    );

this.supabase.client
    .from("users").select("*, teams(team_name)").in(
      "teams.team_name",
      ["Blue"],
    ).then(
      (res) => {},
    );

But this query doesn't work as expected. It seems to be including users who are not part of the specified teams. For instance, I'm getting a response that includes a user who is not in any team:

[
  {"id":1,"name":"Anne","members":[{"teams": {"team_name": "Blue"}}]},
  {"id":2,"name":"Bob","members":[{"teams": {"team_name": "Yellow"}}]},
  {"id":3,"name":"Alice","members":[{"teams": null}]}
]

[
  {"id":1,"name":"Anne","members":[{"teams": {"team_name": "Blue"}}]},
  {"id":2,"name":"Bob","members":[{"teams": {"team_name": "Yellow"}}]},
  {"id":3,"name":"Alice","members":[{"teams": null}]}
]

[
  {"id":1,"name":"Anne","members":["Blue"]},
  {"id":2,"name":"Bob","members":["Yellow"]},
  {"id":3,"name":"Alice","members":[]}
]

[
  {"id":1,"name":"Anne","members":["Blue"]},
  {"id":2,"name":"Bob","members":["Yellow"]},
  {"id":3,"name":"Alice","members":[]}
]

Any help is appreciated! Thank you in advance!

Filtering Users by Team Membership in Supabase Query

Filtering Users by Team Membership in Supabase Query

Similar Threads

Similar Threads

Similar Threads