Joins and related rows

Read rows with their related rows in one request: many-to-one, one-to-many, many-to-many, nested, filtered, inner or left, and views.

Your foreign keys are the joins. Ask for a post with its author and its comments, and they arrive nested in one request, each related table checked against its own row-level security. If you know GORM, this is Preload and Joins, from the browser.

The tables on this page#

sql

create table profiles (
  id uuid primary key default auth.uid(),
  username text unique not null,
  country text
);
create table posts (
  id bigint generated always as identity primary key,
  author_id uuid not null default auth.uid() references profiles (id),
  title text not null
);
create table comments (
  id bigint generated always as identity primary key,
  post_id bigint not null references posts (id) on delete cascade,
  author_id uuid not null default auth.uid() references profiles (id),
  body text not null,
  approved boolean not null default false
);
create table tags (id bigint generated always as identity primary key, name text unique not null);
create table post_tags (
  post_id bigint references posts (id) on delete cascade,
  tag_id bigint references tags (id) on delete cascade,
  primary key (post_id, tag_id)
);
create table follows (
  follower_id uuid references profiles (id),
  followee_id uuid references profiles (id),
  primary key (follower_id, followee_id)
);

alter table profiles enable row level security;
alter table posts enable row level security;
alter table comments enable row level security;
alter table tags enable row level security;
alter table post_tags enable row level security;
alter table follows enable row level security;

create policy read_all on profiles for select using (true);
create policy read_all on posts for select using (true);
create policy read_all on comments for select using (true);
create policy read_all on tags for select using (true);
create policy read_all on post_tags for select using (true);
create policy read_all on follows for select using (true);
create policy own on profiles for insert to authenticated with check (id = auth.uid());
create policy own on posts for insert to authenticated with check (author_id = auth.uid());
create policy own on comments for insert to authenticated with check (author_id = auth.uid());
create policy add on tags for insert to authenticated with check (true);
create policy own on post_tags for insert to authenticated
  with check (exists (select 1 from posts where id = post_id and author_id = auth.uid()));

import { createClient } from '@plexivo/client';

const db = createClient(PLEXIVO_DATA_URL, PLEXIVO_PUBLISHABLE_KEY);
await db.auth.signUp({ email, password });

await db.from('profiles').create({ username: 'asha', country: 'IN' });
const [hello] = await db.from('posts').create([{ title: 'Hello' }, { title: 'World' }]);
await db.from('comments').create([
  { post_id: hello.id, body: 'First!' },
  { post_id: hello.id, body: 'Lovely post', approved: true }
]);
const tag = await db.from('tags').create({ name: 'postgres' });
await db.from('post_tags').create({ post_id: hello.id, tag_id: tag.id });

dart

import 'package:plexivo_client/plexivo_client.dart';

final db = PlexivoClient(dataUrl, publishableKey);
await db.signUp(email: email, password: password);

await db.from('profiles').create({'username': 'ravi', 'country': 'IN'});
final hello = await db.from('posts').create({'title': 'Hello from Dart'});
await db.from('comments').create({'post_id': hello['id'], 'body': 'Lovely post', 'approved': true});
final tag = await db.from('tags').create({'name': 'flutter'});
await db.from('post_tags').create({'post_id': hello['id'], 'tag_id': tag['id']});

Many-to-one and one-to-many#

Name a related table in preload, or in a Supabase-style select. A parent arrives as an object, children as a list:

// Each post with its author (posts.author_id → profiles)
const posts = await db.from('posts').preload('profiles').order('id');
console.log(posts[0].profiles.username);

// Each profile with its posts
const people = await db.from('profiles').preload('posts');

// The same, choosing columns and names
const { data } = await db.from('posts').select('id, title, author:profiles(username), comments(body)');

dart

final posts = await db.from('posts').preload('profiles').order('id');
print(posts.first['profiles']['username']);

final rows = await db.from('posts').select('id, title, author:profiles(username), comments(body)');

Nested#

Follow relations as deep as four levels, with a path or nested selects:

// Posts, their comments, and each comment’s author (GORM: Preload("Comments.Author"))
const threads = await db.from('posts').preload('comments.profiles');

const { data: same } = await db.from('posts').select('title, comments(body, author:profiles(username))');

dart

final threads = await db.from('posts').preload('comments.profiles');

Many-to-many#

Declare the link table once on a model, then preload through it, as with GORM’s many2many:

import { manyToMany } from '@plexivo/client';

const Post = db.model('posts', {
  relations: {
    tags: manyToMany('tags', { through: 'post_tags', foreignKey: 'post_id', otherKey: 'tag_id' })
  }
});

const tagged = await Post.query().preload('tags').order('id');
console.log(tagged[0].tags.map((t) => t.name));

dart

db.model('posts', relations: {
  'tags': const PlexivoRelation.manyToMany('tags', through: 'post_tags', foreignKey: 'post_id', otherKey: 'tag_id'),
});

final tagged = await db.from('posts').preload('tags');

Without a model, select through the link table: select('title, post_tags(tags(name))').

Filter, order and limit related rows#

The related rows take their own conditions, order and limit, per parent row:

const withApproved = await db.from('posts')
  .preload('comments', (c) => c.where('approved', true).order('id', 'desc').limit(5));

// Supabase style
const latest = await db.from('posts')
  .select('title, comments(id, body)')
  .order('id', { referencedTable: 'comments', ascending: false })
  .limit(3, { referencedTable: 'comments' });

dart

final withApproved = await db.from('posts')
    .preload('comments', (PlexivoEmbed c) => c.where('approved', true).order('id', 'desc').limit(5));

Inner and left joins#

preload is a left join: every post comes back, with an empty list when it has no comments. joins is an inner join: only posts with at least one matching comment come back.

const discussed = await db.from('posts').joins('comments', (c) => c.where('approved', true));

const { data: alsoDiscussed } = await db.from('posts').select('title, comments!inner(body)');

dart

final discussed = await db.from('posts').joins('comments', (PlexivoEmbed c) => c.where('approved', true));

Filter by related rows#

Keep the rows that have, or do not have, a matching related row, without loading it. Or match against a subquery, which runs under that table’s row-level security:

const approved = await db.from('posts').whereHas('comments', (c) => c.where('approved', true));
const quiet = await db.from('posts').whereDoesntHave('comments');

// GORM: Where("author_id IN (?)", db.Table("profiles").Select("id").Where("country = ?", "IN"))
const fromIndia = await db.from('posts')
  .whereIn('author_id', db.from('profiles').select('id').where('country', 'IN'));

dart

final approved = await db.from('posts').whereHas('comments', (PlexivoEmbed c) => c.where('approved', true));

final fromIndia = await db.from('posts')
    .whereIn('author_id', db.from('profiles').select('id').where('country', 'IN'));

Counts of related rows#

const counted = await db.from('posts').withCount('comments').order('id');
console.log(counted[0].comments_count);

dart

final counted = await db.from('posts').withCount('comments');
print(counted.first['comments_count']);

Two foreign keys to the same table#

When two columns point at the same table, name the foreign key after !. PostgreSQL names it <table>_<column>_fkey unless you chose a name:

const { data: graph } = await db.from('follows')
  .select('follower:profiles!follows_follower_id_fkey(username), followee:profiles!follows_followee_id_fkey(username)');

Any join, as a view#

For a join the client cannot express, such as a report with group by across tables, write it as a view. A view that runs with its caller’s rights (security_invoker) is in the Data API like a table, and each table in it still applies its own row-level security. This is the safe way to give a browser a raw SQL join.

sql

create view post_summaries with (security_invoker = true) as
  select p.id, p.title, pr.username, count(c.id) as comments
  from posts p
  join profiles pr on pr.id = p.author_id
  left join comments c on c.post_id = p.id
  group by p.id, p.title, pr.username;

const summaries = await db.from('post_summaries').order('comments', 'desc').limit(10);

dart

final summaries = await db.from('post_summaries').order('comments', 'desc').limit(10);

From GORM#

GORM	JavaScript and Dart
`Preload("Author")`	`preload('profiles')`
`Preload("Comments", "approved = ?", true)`	`preload('comments', (c) => c.where('approved', true))`
`Preload("Comments.Author")`	`preload('comments.profiles')`
`Joins("Comments")` (inner join)	`joins('comments')`
`Joins("Author").Order("Author.name")`	`order('profiles.username')`
`many2many:post_tags`	`manyToMany('tags', { through: 'post_tags', … })`
`Where("id IN (?)", subquery)`	`whereIn('id', db.from(…).select('id').where(…))`
`Where("EXISTS (…)")`	`whereHas`, `whereDoesntHave`
`Association("Comments").Count()`	`withCount('comments')`
`Raw("SELECT … JOIN …")`	A `security_invoker` view, or raw SQL on your server

Next steps#

WritesInsert, upsert, update and delete. Transactions and functionsAll-or-nothing writes and your own SQL.

Updated 28 October 2026