pg::cast6.6.2

Joins ​

Get rows with the rows they relate to, in one query and one typed result: a film with its cast, a crew member with their mentor. You nest a shortcut in the lateral option of select(); it becomes a LEFT JOIN LATERAL, and its result a key of each row. You already know select() from the tutorial.

Add the rows that point to it ​

A film's cast is the roles whose film_id is the film's id. Nest a select() of roles under a key of lateral, and name the film's column with db.parent():

TSYou write Open in playground

ts
const 
film
 = await db.
selectOne
('films', { 
id
: 1000 }, {
  
columns
: ['title'],
  
lateral
: {
    
cast
: db.
select
('roles', { 
film_id
: db.
parent
('id') }, {
      
columns
: ['character', 'billing'],
      
order
: { 
by
: 'billing', 
direction
: 'ASC' },
    }),
  },
}).
run
(
pool
);

SQLPostgres runs

sql
SELECT jsonb_build_object($1::text, "title") ||
  jsonb_build_object($2::text, "lateral_cast".result) AS result
FROM "films"
LEFT JOIN LATERAL (
  SELECT coalesce(jsonb_agg(result), '[]') AS result
  FROM (
    SELECT jsonb_build_object($3::text, "character", $4::text, "billing") AS result
    FROM "roles"
    WHERE ("film_id" = "films"."id")
    ORDER BY "billing" ASC
  ) AS "sq_roles"
) AS "lateral_cast" ON true
WHERE ("id" = $5)
LIMIT $6

values ["title", "cast", "character", "billing", 1000, 1]

::TypeScript knows

ts
const film: (db.JSONOnlyColsForTable<"films", "title"[]> & db.LateralResult<{
  cast: db.SQLFragment<db.JSONOnlyColsForTable<"roles", ("billing" | "character")[]>[], never>;
}>) | undefined
// that is
const film: {
  title: string;
  cast: db.JSONOnlyColsForTable<"roles", ("billing" | "character")[]>[];
} | undefined
  • cast becomes a key of the film, holding an array: [] when no role matches.
  • db.parent('id') is "films"."id", a column of the query around it (parent()). When the two columns share a name, db.parent() alone takes the column of its filter.
  • The subquery takes the options of any select(), here columns and order (Options).

Add the row it points to ​

Each film has one studio: studio_id is NOT NULL REFERENCES studios(id), so the row is always there. Nest selectExactlyOne(), and the key holds that row, not an array:

TSYou write Open in playground

ts
const 
film
 = await db.
selectOne
('films', { 
id
: 1000 }, {
  
columns
: ['title'],
  
lateral
: {
    
studio
: db.
selectExactlyOne
('studios', { 
id
: db.
parent
('studio_id') }, { 
columns
: ['name'] }),
    
cast
: db.
select
('roles', { 
film_id
: db.
parent
('id') }, {
      
columns
: ['character', 'billing'],
      
order
: { 
by
: 'billing', 
direction
: 'ASC' },
    }),
  },
}).
run
(
pool
);

SQLPostgres runs

sql
SELECT jsonb_build_object($1::text, "title") ||
  jsonb_build_object($2::text, "lateral_cast".result, $3::text, "lateral_studio".result) AS result
FROM "films"
LEFT JOIN LATERAL (
  SELECT coalesce(jsonb_agg(result), '[]') AS result
  FROM (
    SELECT jsonb_build_object($4::text, "character", $5::text, "billing") AS result
    FROM "roles"
    WHERE ("film_id" = "films"."id")
    ORDER BY "billing" ASC
  ) AS "sq_roles"
) AS "lateral_cast" ON true
LEFT JOIN LATERAL (
  SELECT jsonb_build_object($6::text, "name") AS result
  FROM "studios"
  WHERE ("id" = "films"."studio_id")
  LIMIT $7
) AS "lateral_studio" ON true
WHERE ("id" = $8)
LIMIT $9

values ["title", "cast", "studio", "character", "billing", "name", 1, 1000, 1]

::TypeScript knows

ts
const film: (db.JSONOnlyColsForTable<"films", "title"[]> & db.LateralResult<{
  studio: db.SQLFragment<db.JSONOnlyColsForTable<"studios", "name"[]>, never>;
  cast: db.SQLFragment<db.JSONOnlyColsForTable<"roles", ("billing" | "character")[]>[], never>;
}>) | undefined
// that is
const film: {
  title: string;
  studio: db.JSONOnlyColsForTable<"studios", "name"[]>;
  cast: db.JSONOnlyColsForTable<"roles", ("billing" | "character")[]>[];
} | undefined
  • The joins come in the sorted order of their keys, whatever order you write them in.
  • On a nullable foreign key, nest selectOne(): the key holds the row or null, as the mentor below.
  • In a lateral, selectExactlyOne() checks nothing: a missing row gives null and no error, though its type has no null (selectExactlyOne()). Keep it for a NOT NULL foreign key.

Go through a join table ​

Films and actors are many-to-many, through roles. Nest a lateral in the lateral, to give each role its actor:

TSYou write Open in playground

ts
const 
film
 = await db.
selectOne
('films', { 
id
: 1000 }, {
  
columns
: ['title'],
  
lateral
: {
    
cast
: db.
select
('roles', { 
film_id
: db.
parent
('id') }, {
      
columns
: ['character'],
      
order
: { 
by
: 'billing', 
direction
: 'ASC' },
      
lateral
: {
        
actor
: db.
selectExactlyOne
('actors', { 
id
: db.
parent
('actor_id') }, { 
columns
: ['name'] }),
      },
    }),
  },
}).
run
(
pool
);

SQLPostgres runs

sql
SELECT jsonb_build_object($1::text, "title") ||
  jsonb_build_object($2::text, "lateral_cast".result) AS result
FROM "films"
LEFT JOIN LATERAL (
  SELECT coalesce(jsonb_agg(result), '[]') AS result
  FROM (
    SELECT jsonb_build_object($3::text, "character") ||
      jsonb_build_object($4::text, "lateral_actor".result) AS result
    FROM "roles"
    LEFT JOIN LATERAL (
      SELECT jsonb_build_object($5::text, "name") AS result
      FROM "actors"
      WHERE ("id" = "roles"."actor_id")
      LIMIT $6
    ) AS "lateral_actor" ON true
    WHERE ("film_id" = "films"."id")
    ORDER BY "billing" ASC
  ) AS "sq_roles"
) AS "lateral_cast" ON true
WHERE ("id" = $7)
LIMIT $8

values ["title", "cast", "character", "actor", "name", 1, 1000, 1]

::TypeScript knows

ts
const film: (db.JSONOnlyColsForTable<"films", "title"[]> & db.LateralResult<{
  cast: db.SQLFragment<(db.JSONOnlyColsForTable<"roles", "character"[]> & db.LateralResult<{
    actor: db.SQLFragment<db.JSONOnlyColsForTable<"actors", "name"[]>, never>;
  }>)[], never>;
}>) | undefined
// that is
const film: {
  title: string;
  cast: (db.JSONOnlyColsForTable<"roles", "character"[]> & db.LateralResult<{
    actor: db.SQLFragment<db.JSONOnlyColsForTable<"actors", "name"[]>, never>;
  }>)[];
} | undefined

db.parent() names a column of the nearest query around it: in actor, "roles"."actor_id". Each role's actor is { name: string }. Nest as deep as you need; each level adds a LEFT JOIN LATERAL to the same query.

Keep only the far side ​

For the actors alone, without the role's columns, give the inner lateral one subquery rather than an object: its result takes the place of each role.

TSYou write Open in playground

ts
const 
film
 = await db.
selectOne
('films', { 
id
: 1000 }, {
  
columns
: ['title'],
  
lateral
: {
    
actors
: db.
select
('roles', { 
film_id
: db.
parent
('id') }, {
      
order
: { 
by
: 'billing', 
direction
: 'ASC' },
      
lateral
: db.
selectExactlyOne
('actors', { 
id
: db.
parent
('actor_id') }),
    }),
  },
}).
run
(
pool
);

SQLPostgres runs

sql
SELECT jsonb_build_object($1::text, "title") ||
  jsonb_build_object($2::text, "lateral_actors".result) AS result
FROM "films"
LEFT JOIN LATERAL (
  SELECT coalesce(jsonb_agg(result), '[]') AS result
  FROM (
    SELECT "lateral_passthru".result AS result
    FROM "roles"
    LEFT JOIN LATERAL (
      SELECT to_jsonb("actors".*) AS result
      FROM "actors"
      WHERE ("id" = "roles"."actor_id")
      LIMIT $3
    ) AS "lateral_passthru" ON true
    WHERE ("film_id" = "films"."id")
    ORDER BY "billing" ASC
  ) AS "sq_roles"
) AS "lateral_actors" ON true
WHERE ("id" = $4)
LIMIT $5

values ["title", "actors", 1, 1000, 1]

::TypeScript knows

ts
const film: (db.JSONOnlyColsForTable<"films", "title"[]> & db.LateralResult<{
  actors: db.SQLFragment<actors.JSONSelectable[], never>;
}>) | undefined
// that is
const film: {
  title: string;
  actors: actors.JSONSelectable[];
} | undefined

The order still sorts the roles, so the actors come in billing order. With the role gone, columns and extras on roles would have nothing to add to: the type refuses them, with strictNullChecks on.

ts
db.
select
('roles', { 
film_id
: 1000 }, {
  
columns
: ['character'],
  lateral: db.
selectExactlyOne
('actors', { 
id
: db.
parent
('actor_id') }),
Type 'SQLFragment<JSONSelectable, never>' is not assignable to type 'LimitedLateralOption'.
  Index signature for type 'string' is missing in type 'SQLFragment<JSONSelectable, never>'.
});

Join a table to itself ​

A crew member's mentor is another row of crew. Give the subquery an alias, so that db.parent() still reaches the outer crew:

TSYou write Open in playground

ts
const 
crew
 = await db.
select
('crew', db.
all
, {
  
columns
: ['name', 'job'],
  
order
: { 
by
: 'id', 
direction
: 'ASC' },
  
lateral
: {
    
mentor
: db.
selectOne
('crew', { 
id
: db.
parent
('mentor_id') }, { 
alias
: 'mentor', 
columns
: ['name'] }),
    
proteges
: db.
count
('crew', { 
mentor_id
: db.
parent
('id') }, { 
alias
: 'protege' }),
  },
}).
run
(
pool
);

SQLPostgres runs

sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "name", $2::text, "job") ||
    jsonb_build_object($3::text, "lateral_mentor".result, $4::text, "lateral_proteges".result) AS result
  FROM "crew"
  LEFT JOIN LATERAL (
    SELECT jsonb_build_object($5::text, "name") AS result
    FROM "crew" AS "mentor"
    WHERE ("id" = "crew"."mentor_id")
    LIMIT $6
  ) AS "lateral_mentor" ON true
  LEFT JOIN LATERAL (
    SELECT count(*) AS result
    FROM "crew" AS "protege"
    WHERE ("mentor_id" = "crew"."id")
  ) AS "lateral_proteges" ON true
  ORDER BY "id" ASC
) AS "sq_crew"

values ["name", "job", "mentor", "proteges", "name", 1]

::TypeScript knows

ts
const crew: (db.JSONOnlyColsForTable<"crew", ("name" | "job")[]> & db.LateralResult<{
  mentor: db.SQLFragment<db.JSONOnlyColsForTable<"crew", "name"[]> | undefined, never>;
  proteges: db.SQLFragment<number, never>;
}>)[]
// that is
const crew: {
  name: string;
  job: crew_job;
  mentor: db.JSONOnlyColsForTable<"crew", "name"[]> | null;
  proteges: number;
}[]

  • mentor holds the row or null: Wilhelmina Strand has no mentor.

  • count() gives a number, here each member's protégés.

  • Without the aliases, the subquery's FROM "crew" hides the outer one, and "crew"."mentor_id" is the subquery's own row. Postgres runs it with no error, and every mentor comes back null, every count 0:

    sql
    SELECT jsonb_build_object($5::text, "name") AS result
FROM "crew"
WHERE ("id" = "crew"."mentor_id")
LIMIT $6

  • One alias on the outer query does for both: with alias: 'member' there, db.parent('mentor_id') is "member"."mentor_id" (alias).

Order by a computed value ​

Each cinema, and the nearest other one: a self-join again, with the distance as an extras key and as the order. A sql fragment in a subquery takes db.parent() too:

TSYou write Open in playground

ts
const 
cinemas
 = await db.
select
('cinemas', db.
all
, {
  
columns
: ['name'],
  
lateral
: {
    
nearest
: db.
selectOne
('cinemas', { 
id
: db.conditions.
ne
(db.
parent
('id')) }, {
      
alias
: 'other',
      
columns
: ['name'],
      
extras
: { 
km
: db.
sql
<
s
.cinemas.
SQL
, number>`${'location'} <-> ${db.
parent
('location')}` },
      
order
: { 
by
: db.
sql
<
s
.cinemas.
SQL
>`${'location'} <-> ${db.
parent
('location')}`, 
direction
: 'ASC' },
    }),
  },
}).
run
(
pool
);

SQLPostgres runs

sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "name") ||
    jsonb_build_object($2::text, "lateral_nearest".result) AS result
  FROM "cinemas"
  LEFT JOIN LATERAL (
    SELECT jsonb_build_object($3::text, "name") ||
      jsonb_build_object($4::text, "location" <-> "cinemas"."location") AS result
    FROM "cinemas" AS "other"
    WHERE (("id" <> "cinemas"."id"))
    ORDER BY "location" <-> "cinemas"."location" ASC
    LIMIT $5
  ) AS "lateral_nearest" ON true
) AS "sq_cinemas"

values ["name", "nearest", "name", "km", 1]

::TypeScript knows

ts
const cinemas: (db.JSONOnlyColsForTable<"cinemas", "name"[]> & db.LateralResult<{
  nearest: db.SQLFragment<db.ReturningTypeForTable<"cinemas", "name"[], {
    km: db.SQLFragment<number, never>;
  }> | undefined, never>;
}>)[]
// that is
const cinemas: {
  name: string;
  nearest: (db.JSONOnlyColsForTable<"cinemas", "name"[]> & db.ExtrasResult<"cinemas", {
    km: db.SQLFragment<number, never>;
  }>) | null;
}[]
  • <-> is Postgres's distance between two points: kilometres here, as location is in kilometres east and north of the city centre.
  • km is a number because the fragment says so: db.sql<s.cinemas.SQL, number>.
  • ne(db.parent('id')) leaves the cinema itself out.
  • For the three nearest, nest select() with limit: 3: nearest becomes an array.

Watch for ​

  • strictNullChecks: without it (or strict), columns, extras and lateral leave the type as the full row, and it refuses nothing (Strict).
  • An unchecked join: db.parent() takes a column of any table, so a join on the wrong column compiles. Postgres refuses a column the parent doesn't have: db.parent('billing') under films fails with 42703, "column films.billing does not exist".
  • withTies in a nested selectOne(): it writes FETCH FIRST $2 ROWS WITH TIES, which keeps every tied row: under an outer select(), the parent row comes back once per tie; under selectOne(), with one of them. In a nested select(), the ties stay in the array. Unchanged from Zapatos 6.6.1, and open as #164.
  • A lock: without of, FOR UPDATE reaches the subqueries, and Postgres refuses the query (0A000): "FOR UPDATE is not allowed with aggregate functions" under select() or count(), "FOR UPDATE cannot be applied to the nullable side of an outer join" under selectOne(). Name the table (lock):

TSYou write Open in playground

ts
const 
film
 = await db.
selectOne
('films', { 
id
: 1000 }, {
  
columns
: ['title'],
  
lateral
: { 
cast
: db.
select
('roles', { 
film_id
: db.
parent
('id') }, { 
columns
: ['character'] }) },
  
lock
: { 
for
: 'UPDATE', 
of
: 'films' },
}).
run
(
pool
);

SQLPostgres runs

sql
SELECT jsonb_build_object($1::text, "title") ||
  jsonb_build_object($2::text, "lateral_cast".result) AS result
FROM "films"
LEFT JOIN LATERAL (
  SELECT coalesce(jsonb_agg(result), '[]') AS result
  FROM (
    SELECT jsonb_build_object($3::text, "character") AS result
    FROM "roles"
    WHERE ("film_id" = "films"."id")
  ) AS "sq_roles"
) AS "lateral_cast" ON true
WHERE ("id" = $4)
LIMIT $5
FOR UPDATE OF "films"

values ["title", "cast", "character", 1000, 1]

::TypeScript knows

ts
const film: (db.JSONOnlyColsForTable<"films", "title"[]> & db.LateralResult<{
  cast: db.SQLFragment<db.JSONOnlyColsForTable<"roles", "character"[]>[], never>;
}>) | undefined
// that is
const film: {
  title: string;
  cast: db.JSONOnlyColsForTable<"roles", "character"[]>[];
} | undefined

FOR UPDATE OF "films" locks the film's row, and only it, until the transaction ends: run it on a txnClient. The rows of roles are read, not locked.

See also ​

pgcast is maintained by Alexandre Demartin. Zapatos and its original documentation are by George MacKerron. MIT licence. What changed in each version: the changelog.