Hello team, I have 2 doubts of using the supabase lib. First. I have 3 tables to handle roles that can interact with tables based on the user relationship with the project, 4 tables:

users

,

projects

,

roles

and

projectMembers

with Primary keys

user_id, project_id

and a single

role_id

per user/project (not primary). I want to know if a given user is capable of reading a random table if we know the project owner of the table and the user itself. So i use this:

javascript
const { data, error } = await supabase
    .from("users")
    .select("name, projects(name, role:roles(name))")
    .eq("id", 1) // id of the user
    .eq("projects.id", 2) // id of the project
    .single();

The problem is that, even if i fix the FK of the project to 2 I still get an array for projects, and even if only one role can be assigned, I also get and array of objects. Is it there any way i can make foreign tables to be single if I know they should be only one??? This is what I get for example from the previous project:

json
{
  name: 'John',
  projects: [
    { name: 'ACME', role: [ { name: 'project_admin' } ] }
  ]
}

Also, another weird thing with the API, If i filter a foreign key and nothing is found, I still get all the table with

[]

in the foreign key. How can I remove entries that are empty or not found in foreign tables from the main table. Example:

javascript
const { data, error } = await supabase
    .from("users")
    .select("name, projects(name)")
    .eq("projects.id", 9999) // nobody is asigned to this project.

instead of getting

[]

I get all the users but with empty foreign key like this:

json
[
 {
   name: 'John',
   projects: []
 },
 {
   name: 'Mike',
   projects: []
 },
 // and all the entire list of users. When nothing should actually be returned.
]

I don't undertand but I think this issues are unavoidable in the Javascript library, aren't they?

> How can I remove entries that are empty or not found in foreign tables from the main table. This one should be fixed with https://github.com/supabase/postgrest-js/issues/197

Thanks, good to know that the team is aware, it was pretty hard to search for this issue using keywords. I will be subscribed to updates on it.