英文:
Querying sub-sub table in Supabase / Grandchild relationship in JOIN QUERY
问题
给定以下架构：
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));
我想查询 teams 表，同时返回 projects 表中的 products。是否有方法可以实现这个需求？
英文:
Given the following schema:
<img src="https://i.stack.imgur.com/NrfjU.png" width="300" />
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?
答案1
得分: 1
以下是翻译好的部分：
对于单一关系设置，您可以执行以下操作（假设为自上而下的关系：
organization -> teams 和 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));
在这种情况下，这是不可能的，您将收到以下错误消息：
> 无法嵌入，因为找到了超过一个关系 'organization' 和 'teams'。
在这种情况下，您可以在调用 supabase 时选择关系，要么：
teams!projects 或 teams!teams_org_name_fkey。前者在大多数情况下更为推荐。
const supabase = createClient(SUPABASE_URL, SUPABASE_KEY);const { data: ret, error } = await supabase.from('organization').select(`*,teams!projects(*,projects(products))`);
输出：
> [{"org_name":"Contoso","teams":[{"org_name":"Contoso","team_name":"Contoso Café","projects":[{"products":{"Dairy":"latte","coffee":["french press","expresso","cold brew"]}}]}]}
英文:
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"]}}]}]}]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论