mirror of
https://github.com/supabase/supabase.git
synced 2026-10-17 15:19:08 +03:00
538 lines
9.5 KiB
Plaintext
538 lines
9.5 KiB
Plaintext
---
|
|
id: 'joins-and-nested-tables'
|
|
title: 'Querying Joins and Nested tables'
|
|
description: 'The Data APIs automatically detect relationships between Postgres tables.'
|
|
---
|
|
|
|
The data 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`:
|
|
|
|
<Tabs
|
|
scrollable
|
|
size="small"
|
|
type="underlined"
|
|
defaultActiveId="table"
|
|
queryGroup="output-format"
|
|
>
|
|
<TabPanel id="table" label="Tables">
|
|
|
|
**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 |
|
|
|
|
</TabPanel>
|
|
<TabPanel id="SQL" label="SQL">
|
|
|
|
```sql
|
|
create table countries (
|
|
"id" serial primary key,
|
|
"name" text
|
|
);
|
|
|
|
insert into countries
|
|
(id, name)
|
|
values
|
|
(1, 'United Kingdom'),
|
|
(2, 'United States');
|
|
|
|
create table cities (
|
|
"id" serial primary key,
|
|
"name" text,
|
|
"country_id" int references "countries"
|
|
);
|
|
|
|
insert into cities
|
|
(name, country_id)
|
|
values
|
|
('London', 1),
|
|
('Manchester', 1),
|
|
('Los Angeles', 2),
|
|
('New York', 2);
|
|
```
|
|
|
|
</TabPanel>
|
|
</Tabs>
|
|
|
|
The APIs will automatically detect relationships based on the foreign keys:
|
|
|
|
<Tabs
|
|
scrollable
|
|
size="small"
|
|
type="underlined"
|
|
defaultActiveId="js"
|
|
queryGroup="language"
|
|
>
|
|
<TabPanel id="js" label="JavaScript">
|
|
|
|
```js
|
|
const { data, error } = await supabase.from('countries').select(`
|
|
id,
|
|
name,
|
|
cities ( id, name )
|
|
`)
|
|
```
|
|
|
|
### TypeScript types for joins
|
|
|
|
`supabase-js` always returns a `data` object (for success), and an `error` object (for unsuccessful requests).
|
|
|
|
These helper types provide the result types from any query, including nested types for database joins.
|
|
|
|
Given the following schema with a relation between cities and countries:
|
|
|
|
```sql
|
|
create table countries (
|
|
"id" serial primary key,
|
|
"name" text
|
|
);
|
|
|
|
create table cities (
|
|
"id" serial primary key,
|
|
"name" text,
|
|
"country_id" int references "countries"
|
|
);
|
|
```
|
|
|
|
We can get the nested `CountriesWithCities` type like this:
|
|
|
|
```ts
|
|
import { QueryResult, QueryData, QueryError } from '@supabase/supabase-js'
|
|
|
|
const countriesWithCitiesQuery = supabase.from('countries').select(`
|
|
id,
|
|
name,
|
|
cities (
|
|
id,
|
|
name
|
|
)
|
|
`)
|
|
type CountriesWithCities = QueryData<typeof countriesWithCitiesQuery>
|
|
|
|
const { data, error } = await countriesWithCitiesQuery
|
|
if (error) throw error
|
|
const countriesWithCities: CountriesWithCities = data
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="dart" label="Dart">
|
|
|
|
```dart
|
|
final data = await supabase.from('countries').select('id, name, cities(id, name)');
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="swift" label="Swift">
|
|
|
|
```swift
|
|
struct Country: Codable {
|
|
let id: Int
|
|
let name: String
|
|
let cities: [City]
|
|
|
|
struct City: Codable {
|
|
let id: Int
|
|
let name: String
|
|
}
|
|
}
|
|
|
|
let countries: [Country] = try await supabase
|
|
.from("countries")
|
|
.select("id, name, cities(id, name)")
|
|
.execute()
|
|
.value
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="kotlin" label="Kotlin">
|
|
|
|
```kotlin
|
|
val data = supabase.from("countries").select(Columns.raw("id, name, cities(id, name)"))
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="python" label="Python">
|
|
|
|
```python
|
|
data = supabase.from_('countries').select('id, name, cities(id, name)').execute()
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="graphql" label="GraphQL">
|
|
|
|
```javascript
|
|
const Query = `
|
|
query {
|
|
countriesCollection {
|
|
edges {
|
|
node {
|
|
id
|
|
name
|
|
cities {
|
|
id,
|
|
name
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
`
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="url" label="URL">
|
|
|
|
```bash
|
|
GET https://[REF].supabase.co/rest/v1/countries?select=id,name,cities(id,name)
|
|
```
|
|
|
|
</TabPanel>
|
|
</Tabs>
|
|
|
|
## Many-to-many joins
|
|
|
|
The data 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):
|
|
|
|
```sql
|
|
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)
|
|
);
|
|
```
|
|
|
|
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:
|
|
|
|
<Tabs
|
|
scrollable
|
|
size="small"
|
|
type="underlined"
|
|
defaultActiveId="js"
|
|
queryGroup="language"
|
|
>
|
|
<TabPanel id="js" label="JavaScript">
|
|
|
|
```js
|
|
const { data, error } = await supabase.from('teams').select(`
|
|
id,
|
|
team_name,
|
|
users ( id, name )
|
|
`)
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="dart" label="Dart">
|
|
|
|
```dart
|
|
final data = await supabase.from('teams').select('id, team_name, users(id, name)');
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="swift" label="Swift">
|
|
|
|
```swift
|
|
struct Team: Codable {
|
|
let id: Int
|
|
let name: String
|
|
let users: [User]
|
|
|
|
struct User: Codable {
|
|
let id: Int
|
|
let name: String
|
|
}
|
|
|
|
enum CodingKeys: String, CodingKey {
|
|
case id, users
|
|
case name = "team_name"
|
|
}
|
|
}
|
|
let teams [Team] = try await supabase
|
|
.from("teams")
|
|
.select(
|
|
"""
|
|
id,
|
|
team_name,
|
|
users ( id, name )
|
|
"""
|
|
)
|
|
.execute()
|
|
.value
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="kotlin" label="Kotlin">
|
|
|
|
```kotlin
|
|
val data = supabase.from("teams").select(Columns.raw("id, team_name, users(id, name)"));
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="python" label="Python">
|
|
|
|
```python
|
|
data = supabase.from_('teams').select('id, team_name, users(id, name)').execute()
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="graphql" label="GraphQL">
|
|
|
|
````javascript
|
|
const Query = `
|
|
query {
|
|
|
|
</TabPanel>
|
|
<TabPanel id="graphql" label="GraphQL">
|
|
|
|
```javascript
|
|
const Query = `
|
|
query {
|
|
teamsCollection {
|
|
edges {
|
|
node {
|
|
id
|
|
team_name
|
|
users {
|
|
id,
|
|
name
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
`
|
|
````
|
|
|
|
</TabPanel>
|
|
<TabPanel id="url" label="URL">
|
|
|
|
```bash
|
|
GET https://[REF].supabase.co/rest/v1/teams?select=id,team_name,users(id,name)
|
|
```
|
|
|
|
</TabPanel>
|
|
</Tabs>
|
|
|
|
## Specifying the `ON` clause for joins with multiple foreign keys
|
|
|
|
For example, if you have a project that tracks when employees check in and out of work shifts:
|
|
|
|
```sql
|
|
-- Employees
|
|
create table users (
|
|
"id" serial primary key,
|
|
"name" text
|
|
);
|
|
|
|
-- Badge scans
|
|
create table scans (
|
|
"id" serial primary key,
|
|
"user_id" int references users,
|
|
"badge_scan_time" timestamp
|
|
);
|
|
|
|
-- Work shifts
|
|
create table shifts (
|
|
"id" serial primary key,
|
|
"user_id" int references users,
|
|
"scan_id_start" int references scans, -- clocking in
|
|
"scan_id_end" int references scans, -- clocking out
|
|
"attendance_status" text
|
|
);
|
|
```
|
|
|
|
In this case, you need to explicitly define the join because the joining column on `shifts` is ambiguous as they are both referencing the `scans` table.
|
|
|
|
To fetch all the `shifts` with `scan_id_start` and `scan_id_end` related to a specific `scan`, use the following syntax:
|
|
|
|
<Tabs
|
|
scrollable
|
|
size="small"
|
|
type="underlined"
|
|
defaultActiveId="js"
|
|
queryGroup="language"
|
|
>
|
|
<TabPanel id="js" label="JavaScript">
|
|
|
|
```js
|
|
const { data, error } = await supabase.from('shifts').select(
|
|
`
|
|
*,
|
|
start_scan:scans!scan_id_start (
|
|
id,
|
|
user_id,
|
|
badge_scan_time
|
|
),
|
|
end_scan:scans!scan_id_end (
|
|
id,
|
|
user_id,
|
|
badge_scan_time
|
|
)
|
|
`
|
|
)
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="dart" label="Dart">
|
|
|
|
```dart
|
|
final data = await supabase.from('shifts').select('''
|
|
*,
|
|
start_scan:scans!scan_id_start (
|
|
id,
|
|
user_id,
|
|
badge_scan_time
|
|
),
|
|
end_scan:scans!scan_id_end (
|
|
id,
|
|
user_id,
|
|
badge_scan_time
|
|
)
|
|
''');
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="swift" label="Swift">
|
|
|
|
```swift
|
|
struct Shift: Codable {
|
|
let id: Int
|
|
let userId: Int
|
|
let attendanceStatus: String?
|
|
|
|
let scans: [Scan]
|
|
|
|
struct Scan: Codable {
|
|
let id: Int
|
|
let userId: Int
|
|
let badgeScanTime: TimeInterval
|
|
|
|
enum CodingKeys: String, CodingKey {
|
|
case id
|
|
case userId = "user_id"
|
|
case badgeScanTime = "badge_scan_time"
|
|
}
|
|
}
|
|
|
|
enum CodingKeys: String, CodingKey {
|
|
case id
|
|
case userId = "user_id"
|
|
case attendanceStatus = "attendance_status"
|
|
}
|
|
}
|
|
|
|
let shifts: [Shift] = try await supabase
|
|
.from("shifts")
|
|
.select(
|
|
"""
|
|
*,
|
|
start_scan:scans!scan_id_start (
|
|
id,
|
|
user_id,
|
|
badge_scan_time
|
|
),
|
|
scans: scan_id_end (
|
|
id,
|
|
user_id,
|
|
badge_scan_time
|
|
)
|
|
"""
|
|
)
|
|
.execute()
|
|
.value
|
|
```
|
|
|
|
</TabPanel>
|
|
|
|
<TabPanel id="kotlin" label="Kotlin">
|
|
|
|
```kotlin
|
|
val data = supabase.from("shifts").select(Columns.raw('''
|
|
*,
|
|
start_scan:scans!scan_id_start (
|
|
id,
|
|
user_id,
|
|
badge_scan_time
|
|
),
|
|
end_scan:scans!scan_id_end (
|
|
id,
|
|
user_id,
|
|
badge_scan_time
|
|
)
|
|
'''));
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="python" label="Python">
|
|
|
|
```python
|
|
data = supabase.from_('shifts').select("""
|
|
*,
|
|
start_scan:scans!scan_id_start (
|
|
id,
|
|
user_id,
|
|
badge_scan_time
|
|
),
|
|
end_scan:scans!scan_id_end (
|
|
id,
|
|
user_id,
|
|
badge_scan_time
|
|
)
|
|
""").execute()
|
|
```
|
|
|
|
</TabPanel>
|
|
<TabPanel id="graphql" label="GraphQL">
|
|
|
|
```javascript
|
|
const Query = `
|
|
query {
|
|
shiftsCollection {
|
|
edges {
|
|
node {
|
|
id
|
|
user_id
|
|
attendance_status
|
|
scan_id_start {
|
|
id
|
|
user_id
|
|
badge_scan_time
|
|
}
|
|
scan_id_end {
|
|
id
|
|
user_id
|
|
badge_scan_time
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
`
|
|
```
|
|
|
|
</TabPanel>
|
|
</Tabs>
|