Work with relations

You’ll declare each kind of relation, create related rows, query across them, and manage a many-to-many link table.

Prerequisites

The blog schema from Define a schema.

Steps

1. Belongs-to and has-many

Already in the blog schema:

Post:
  columns:
    author:
      type: Author
      is_null: false
      on_delete: CASCADE
      reverse_name: posts

Generated	Meaning
`posts.author_id BIGINT NOT NULL` + FK	the stored link
`Post.authorId`, `PostTable.authorId`	the foreign-key value and column
`Post.author`	belongs-to relation, loaded into `post.authorLoaded`
`Author.posts`	has-many relation, loaded into `author.postsList`

Choose on_delete deliberately: CASCADE deletes children with the parent, RESTRICT / NO ACTION refuse to delete a parent that has children, and the default SET NULL needs a nullable column.

2. Has-one

Make the foreign key unique and the inverse becomes has-one:

Profile:
  columns:
    bio: { type: text }
    author:
      type: Author
      is_null: false
      is_unique: true
      on_delete: CASCADE
      reverse_name: profile

Author.profile is a HasOneRelation, loaded into author.profileLoaded (a Profile?).

Write the foreign key value:

import 'package:blog/models/blog.gisila.g.dart';
import 'package:gisila_orm/gisila.dart';

final created = await db.transaction((tx) async {
  final author = await authors().insert({'name': 'Ada', 'email': 'ada@example.com'}).one(tx);
  final post = await posts().insert({'title': 'Hello', 'author_id': author.id}).one(tx);
  await comments().insert({'body': 'Nice', 'post_id': post.id, 'author_id': author.id}).run(tx);
  return post;
});

A transaction keeps the three inserts together. A wrong id fails with PostgresForeignKeyViolationException.

4. Query across a relation

Children of one parent, by foreign key:

final adaPosts = await posts().where(PostTable.authorId.eq(adaId)).all(db.context());

Parents filtered by their children, with a join:

// Authors who have commented on post 7.
final commenters = await authors()
    .join('comments', AuthorTable.id.eqExpr(CommentTable.authorId))
    .where(CommentTable.postId.eq(7))
    .distinct()
    .all(db.context());

Or with EXISTS, which never duplicates rows:

final withPosts = await authors()
    .where(RawSql<bool>('EXISTS (SELECT 1 FROM posts p WHERE p.author_id = "authors"."id")'))
    .all(db.context());

To get related models attached to each result, use eager loading.

5. Many-to-many

Declare it on one side; reverse_name gives the other side:

Tag:
  columns:
    name: { type: varchar, is_null: false, is_unique: true }

Post:
  columns:
    # ...
    labels:
      type: Tag
      many_to_many: true
      reverse_name: posts

The generator creates a junction table named from both table names, sorted: posts_tags, with columns posts_id, tags_id and created_at, a composite primary key, and ON DELETE CASCADE on both foreign keys.

There’s no builder API for junction rows. Link and unlink with SQL:

Future<void> addLabel(DbContext db, int postId, int tagId) => db.execute(
      'INSERT INTO "posts_tags" ("posts_id", "tags_id") VALUES (\$1, \$2) ON CONFLICT DO NOTHING',
      parameters: [postId, tagId],
    );

Future<void> removeLabel(DbContext db, int postId, int tagId) => db.execute(
      'DELETE FROM "posts_tags" WHERE "posts_id" = \$1 AND "tags_id" = \$2',
      parameters: [postId, tagId],
    );

Future<List<Post>> postsTagged(DbContext db, String tag) => posts()
    .where(RawSql<bool>(
      'EXISTS (SELECT 1 FROM "posts_tags" pt JOIN "tags" t ON t."id" = pt."tags_id" '
      'WHERE pt."posts_id" = "posts"."id" AND t."name" = \$1)',
      [tag],
    ))
    .all(db);

If the link needs its own data (a role, a position), make the junction an ordinary model with two foreign keys instead of many_to_many, and give it a unique index over both:

PostTag:
  columns:
    post: { type: Post, is_null: false, on_delete: CASCADE, reverse_name: post_tags }
    tag: { type: Tag, is_null: false, on_delete: CASCADE, reverse_name: post_tags }
    position: { type: integer, is_null: false, default: 0 }
  indexes:
    uq_post_tag:
      columns: [post, tag]
      unique: true

That model works with the normal builder and with preloading (Post.postTags.then(PostTag.tag)).

Verify it works

await addLabel(db.context(), created.id!, tagId);
final tagged = await postsTagged(db.context(), 'dart');
assert(tagged.any((p) => p.id == created.id));