Querying Joins and Nested tables
The Serverless APIs automatically detect relationships between Postgres tables. Since Postgres is a relational database, this is a very common scenario.
One-to-many joins#
Let's use an example database that stores countries and cities:
Countries
id | name |
|---|---|
| 1 | United Kingdom |
| 2 | United States |
Cities
id | name | country_id |
|---|---|---|
| 1 | London | 1 |
| 2 | Manchester | 1 |
| 3 | Los Angeles | 2 |
| 4 | New York | 2 |
The APIs will automatically detect relationships based on the foreign keys:
_10const { data, error } = await supabase.from('countries').select(`_10 id, _10 name, _10 cities ( id, name )_10`)
Many-to-many joins#
The Serverless APIs will detect many-to-many joins. For example, if you have a database which stored teams of users (where each user could belong to many teams):
_15create table users (_15 "id" serial primary key,_15 "name" text_15);_15_15create table teams (_15 "id" serial primary key,_15 "team_name" text_15);_15_15create table members (_15 "user_id" int references users,_15 "team_id" int references teams,_15 primary key (user_id, team_id)_15);
In these cases you don't need to explicitly define the joining table (members). If we wanted to fetch all the teams and the members in each team:
_10const { data, error } = await supabase.from('teams').select(`_10 id, _10 team_name, _10 users ( id, name )_10`)