Database

Querying Joins and Nested 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:

The APIs will automatically detect relationships based on the foreign keys:

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):


_15
create table users (
_15
"id" serial primary key,
_15
"name" text
_15
);
_15
_15
create table teams (
_15
"id" serial primary key,
_15
"team_name" text
_15
);
_15
_15
create 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:

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:


_21
-- Employees
_21
create table users (
_21
"id" serial primary key,
_21
"name" text
_21
);
_21
_21
-- Badge scans
_21
create table scans (
_21
"id" serial primary key,
_21
"user_id" int references users,
_21
"badge_scan_time" timestamp
_21
);
_21
_21
-- Work shifts
_21
create table shifts (
_21
"id" serial primary key,
_21
"user_id" int references users,
_21
"scan_id_start" int references scans, -- clocking in
_21
"scan_id_end" int references scans, -- clocking out
_21
"attendance_status" text
_21
);

In this case, you need to explicitly define the join because the joining column on shifts is ambiguous.

To fetch all the shifts with scan_id_start related to a specific scan, use the following syntax: