Querying sub-sub table in Supabase / Grandchild relationship in JOIN QUERY

Question

Given the following schema:

CREATE TABLE organization (
   org_name text  NOT NULL,
   PRIMARY KEY (org_name)
);

CREATE TABLE teams (
   org_name text NOT NULL,
   team_name text NOT NULL,
   PRIMARY KEY (org_name, team_name),
   FOREIGN KEY (org_name)
      REFERENCES organization (org_name)
);

CREATE TABLE projects (
   org_name text NOT NULL,
   team_name text NOT NULL,
   project_name text NOT NULL,
   products jsonb,
   PRIMARY KEY (org_name, team_name, project_name),
   FOREIGN KEY (org_name)
      REFERENCES organization (org_name),
   FOREIGN KEY (org_name, team_name)
      REFERENCES teams (org_name, team_name)
);

I want to query the teams table but also return the products from the projects table. Is there a way to do this?

Answer 1

For single relationships set, then you can do the following (assuming a top-bottom relationship as:

organization -> teams and teams -> projects

const supabase = createClient(SUPABASE_URL, SUPABASE_KEY);
const { data: ret, error } = await supabase
  .from('organization')
  .select(`*, 
           teams(*,
                 projects(products)
                )
          `);
console.log(JSON.stringify(ret));

In this case, it would not be possible and you would get the following error:

Could not embed because more than one relationship was found for 'organization' and 'teams'

In this case, you can pick the relationship when calling supabase with either: teams!projects or teams!teams_org_name_fkey. The former is preferred for most cases.

const supabase = createClient(SUPABASE_URL, SUPABASE_KEY);
const { data: ret, error } = await supabase
  .from('organization')
  .select(`*, 
          teams!projects(*,
                        projects(products)
                        )
         `);

Output:

[{"org_name":"Contoso","teams":[{"org_name":"Contoso","team_name":"Contoso Café","projects":[{"products":{"Dairy":"latte","coffee":["french press","expresso","cold brew"]}}]}]}]