Working with Entity Relationships

This guide shows you how to implement One-to-Many, Many-to-One, and Many-to-Many relationships in Stack9. We'll build a complete Blog System with Posts, Authors, Categories, and Tags.

What You'll Learn

✅ One-to-Many relationships (Author → Posts)
✅ Many-to-One relationships (Post → Author)
✅ Many-to-Many relationships (Posts ↔ Tags)
✅ Loading related data in queries
✅ Displaying relationships in screens
✅ Cascading operations

Time Required: 30 minutes

Relationship Types Overview

One-to-Many (Parent → Children)
Author ─┬─→ Post 1
        ├─→ Post 2
        └─→ Post 3

Many-to-One (Child → Parent)
Post 1 ─┐
Post 2 ─┼─→ Category
Post 3 ─┘

Many-to-Many (Both Directions)
Post 1 ─┬─→ Tag: JavaScript
        ├─→ Tag: Tutorial
        └─→ Tag: Beginner

Tag: JavaScript ─┬─→ Post 1
                 ├─→ Post 5
                 └─→ Post 8

Example: Blog System

We'll create:

Author (one) → Posts (many)
Post (many) → Category (one)
Post (many) ↔ Tags (many)

Step 1: Create the Author Entity

File: src/entities/custom/author.json

{
  "head": {
    "name": "Author",
    "key": "author",
    "pluralisedName": "authors",
    "icon": "UserOutlined"
  },
  "fields": [
    {
      "label": "Name",
      "key": "name",
      "type": "TextField",
      "validateRules": {
        "required": true,
        "maxLength": 100
      }
    },
    {
      "label": "Email",
      "key": "email",
      "type": "TextField",
      "typeOptions": {
        "format": "email"
      },
      "validateRules": {
        "required": true
      }
    },
    {
      "label": "Bio",
      "key": "bio",
      "type": "RichTextEditor"
    },
    {
      "label": "Posts",
      "key": "posts",
      "type": "Grid",
      "relationshipOptions": {
        "ref": "post"
      },
      "typeOptions": {
        "relationshipField": "author_id"
      }
    }
  ]
}

Key Points:

posts field is type Grid - defines One-to-Many relationship
relationshipOptions.ref points to related entity (post)
relationshipField specifies the foreign key in child table

Step 2: Create the Category Entity

File: src/entities/custom/category.json

{
  "head": {
    "name": "Category",
    "key": "category",
    "pluralisedName": "categories"
  },
  "fields": [
    {
      "label": "Name",
      "key": "name",
      "type": "TextField",
      "validateRules": {
        "required": true
      }
    },
    {
      "label": "Slug",
      "key": "slug",
      "type": "TextField",
      "validateRules": {
        "required": true,
        "pattern": "^[a-z0-9-]+$"
      }
    }
  ]
}

Step 3: Create the Tag Entity

File: src/entities/custom/tag.json

{
  "head": {
    "name": "Tag",
    "key": "tag",
    "pluralisedName": "tags"
  },
  "fields": [
    {
      "label": "Name",
      "key": "name",
      "type": "TextField",
      "validateRules": {
        "required": true,
        "maxLength": 50
      }
    },
    {
      "label": "Color",
      "key": "color",
      "type": "OptionSet",
      "typeOptions": {
        "values": ["blue", "green", "red", "yellow", "purple"]
      },
      "defaultValue": "blue"
    }
  ]
}

Step 4: Create the Post Entity with All Relationships

File: src/entities/custom/post.json

{
  "head": {
    "name": "Post",
    "key": "post",
    "pluralisedName": "posts",
    "icon": "FileTextOutlined",
    "allowComments": true
  },
  "fields": [
    {
      "label": "Title",
      "key": "title",
      "type": "TextField",
      "validateRules": {
        "required": true,
        "maxLength": 200
      }
    },
    {
      "label": "Slug",
      "key": "slug",
      "type": "TextField",
      "validateRules": {
        "required": true,
        "pattern": "^[a-z0-9-]+$"
      }
    },
    {
      "label": "Content",
      "key": "content",
      "type": "RichTextEditor",
      "validateRules": {
        "required": true
      }
    },
    {
      "label": "Author",
      "key": "author_id",
      "type": "SingleDropDown",
      "relationshipOptions": {
        "ref": "author"
      },
      "typeOptions": {
        "label": "name"
      },
      "validateRules": {
        "required": true
      }
    },
    {
      "label": "Category",
      "key": "category_id",
      "type": "SingleDropDown",
      "relationshipOptions": {
        "ref": "category"
      },
      "typeOptions": {
        "label": "name"
      },
      "validateRules": {
        "required": true
      }
    },
    {
      "label": "Tags",
      "key": "tags",
      "type": "MultiDropDown",
      "relationshipOptions": {
        "ref": "tag"
      },
      "typeOptions": {
        "label": "name",
        "value": "id"
      }
    },
    {
      "label": "Status",
      "key": "status",
      "type": "OptionSet",
      "typeOptions": {
        "values": ["Draft", "Published", "Archived"]
      },
      "defaultValue": "Draft"
    },
    {
      "label": "Published Date",
      "key": "published_at",
      "type": "DateField",
      "typeOptions": {
        "time": true
      }
    }
  ]
}

Relationship Types in This Entity:

Many-to-One (Post → Author):
- author_id field
- Type: SingleDropDown
- Each post has one author
Many-to-One (Post → Category):
- category_id field
- Type: SingleDropDown
- Each post belongs to one category
Many-to-Many (Post ↔ Tags):
- tags field
- Type: MultiDropDown
- Posts can have multiple tags
- Tags can belong to multiple posts
- Stack9 auto-creates junction table

Step 5: Query Posts with Relationships

File: src/query-library/getpostlist.json

{
  "key": "getpostlist",
  "name": "getPostList",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/post/search",
    "bodyParams": "{\n  \"$select\": [\n    \"id\",\n    \"title\",\n    \"slug\",\n    \"author_id\",\n    \"author.name\",\n    \"author.email\",\n    \"category_id\",\n    \"category.name\",\n    \"status\",\n    \"published_at\",\n    \"_created_at\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false\n  },\n  \"$withRelated\": [\n    \"author(notDeleted)\",\n    \"category(notDeleted)\",\n    \"tags(notDeleted)\"\n  ],\n  \"$orderBy\": [\n    {\"column\": \"_created_at\", \"order\": \"desc\"}\n  ]\n}"
  }
}

Key Query Features:

$select uses dot notation for related fields: author.name, category.name
$withRelated loads relationships: author, category, tags
(notDeleted) applies scope to only load non-deleted related records

Step 6: Display Relationships in List Screen

File: src/screens/post_list.json

{
  "head": {
    "title": "Posts",
    "key": "post_list",
    "route": "post-list",
    "app": "blog"
  },
  "screenType": "listView",
  "listQuery": "getpostlist",
  "entityKey": "post",
  "columnsConfiguration": [
    {
      "field": "title",
      "label": "Title",
      "value": "{{title}}",
      "renderAs": "Text",
      "options": {
        "linkProp": "/blog/post-detail/{{id}}"
      }
    },
    {
      "field": "author",
      "label": "Author",
      "value": "{{author.name}}",
      "renderAs": "Text",
      "options": {
        "linkProp": "/blog/author-detail/{{author_id}}"
      }
    },
    {
      "field": "category",
      "label": "Category",
      "value": "{{category.name}}",
      "renderAs": "Tag",
      "options": {
        "color": "blue"
      }
    },
    {
      "field": "tags",
      "label": "Tags",
      "value": "{{tags}}",
      "renderAs": "Tags",
      "options": {
        "labelField": "name",
        "colorField": "color"
      }
    },
    {
      "field": "status",
      "label": "Status",
      "value": "{{status}}",
      "renderAs": "EnumTags",
      "options": {
        "colorMapping": {
          "Draft": "orange",
          "Published": "green",
          "Archived": "gray"
        }
      }
    },
    {
      "field": "published_at",
      "label": "Published",
      "value": "{{published_at}}",
      "renderAs": "Date",
      "options": {
        "format": "DD MMM YYYY"
      }
    }
  ],
  "filters": [
    {
      "field": "author_id",
      "label": "Author",
      "type": "dropdown",
      "entityKey": "author"
    },
    {
      "field": "category_id",
      "label": "Category",
      "type": "dropdown",
      "entityKey": "category"
    },
    {
      "field": "status",
      "label": "Status",
      "type": "dropdown",
      "options": ["Draft", "Published", "Archived"]
    }
  ]
}

Displaying Related Data:

Use dot notation: {{author.name}}, {{category.name}}
Tags array: {{tags}} with renderAs: "Tags"
Filter by relationships using entityKey

Step 7: Author Detail with Child Posts

File: src/screens/author_detail.json

{
  "head": {
    "title": "Author Details",
    "key": "author_detail",
    "route": "author-detail/:id",
    "app": "blog"
  },
  "screenType": "detailView",
  "detailQuery": "getauthor",
  "entityKey": "author",
  "sections": [
    {
      "title": "Author Information",
      "fields": ["name", "email", "bio"]
    },
    {
      "title": "Posts by this Author",
      "fields": ["posts"]
    }
  ]
}

File: src/query-library/getauthor.json

{
  "key": "getauthor",
  "name": "getAuthor",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/author/search",
    "bodyParams": "{\n  \"$select\": [\"*\"],\n  \"$where\": {\n    \"id\": {{id}}\n  },\n  \"$withRelated\": [\n    \"posts(notDeleted)\",\n    \"posts(notDeleted).category(notDeleted)\"\n  ]\n}",
    "queryParams": {}
  },
  "userParams": {
    "id": ""
  }
}

Nested Relationships:

posts(notDeleted) loads all author's posts
posts(notDeleted).category(notDeleted) loads category for each post
Grid field automatically displays posts in table

Step 8: Advanced Query - Posts with All Relationships

File: src/query-library/getpost.json

{
  "key": "getpost",
  "name": "getPost",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/post/search",
    "bodyParams": "{\n  \"$select\": [\n    \"*\",\n    \"author.name\",\n    \"author.email\",\n    \"author.bio\",\n    \"category.name\",\n    \"category.slug\"\n  ],\n  \"$where\": {\n    \"id\": {{id}}\n  },\n  \"$withRelated\": [\n    \"author(notDeleted)\",\n    \"category(notDeleted)\",\n    \"tags(notDeleted)\"\n  ]\n}",
    "queryParams": {}
  },
  "userParams": {
    "id": ""
  }
}

Relationship Patterns

Pattern 1: One-to-Many (Parent → Children)

Parent Entity (Author):

{
  "label": "Posts",
  "key": "posts",
  "type": "Grid",
  "relationshipOptions": {
    "ref": "post"
  },
  "typeOptions": {
    "relationshipField": "author_id"
  }
}

Child Entity (Post):

{
  "label": "Author",
  "key": "author_id",
  "type": "SingleDropDown",
  "relationshipOptions": {
    "ref": "author"
  }
}

Pattern 2: Many-to-One (Child → Parent)

Child Entity (Post):

{
  "label": "Category",
  "key": "category_id",
  "type": "SingleDropDown",
  "relationshipOptions": {
    "ref": "category"
  },
  "typeOptions": {
    "label": "name"
  }
}

Parent Entity (Category): No explicit field needed - relationship exists through foreign key.

Pattern 3: Many-to-Many (Both Directions)

Entity A (Post):

{
  "label": "Tags",
  "key": "tags",
  "type": "MultiDropDown",
  "relationshipOptions": {
    "ref": "tag"
  },
  "typeOptions": {
    "label": "name",
    "value": "id"
  }
}

Entity B (Tag):

{
  "label": "Posts",
  "key": "posts",
  "type": "MultiDropDown",
  "relationshipOptions": {
    "ref": "post"
  },
  "typeOptions": {
    "label": "title",
    "value": "id"
  }
}

Stack9 Auto-Creates Junction Table:

CREATE TABLE post_tag (
  post_id INTEGER REFERENCES posts(id),
  tag_id INTEGER REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);

Working with Relationships in Code

Entity Hooks with Relationships

File: src/entity-hooks/post.vat.ts

import {
  CustomFunction,
  CustomFunctionContext,
  CustomFunctionResponse,
  HookOperation,
} from '@april9/stack9-sdk';
import { DBPost } from '../models/stack9/Post';

export class ValidatePost extends CustomFunction {
  constructor(private context: CustomFunctionContext<DBPost>) {
    super();
  }

  entityName = 'post';

  async exec(): Promise<CustomFunctionResponse> {
    const { entity, operation, services } = this.context;

    // Verify author exists
    if (entity.author_id) {
      const author = await services.entity.findOne('author', {}, {
        $where: { id: entity.author_id, _is_deleted: false }
      });

      if (!author) {
        return {
          valid: false,
          errors: [{
            field: 'author_id',
            message: 'Author not found'
          }]
        };
      }
    }

    // Auto-set published date when status changes to Published
    if (operation === HookOperation.update) {
      if (entity.status === 'Published' && !entity.published_at) {
        entity.published_at = new Date();
      }
    }

    return { valid: true, entity };
  }
}

Querying Relationships Programmatically

// Get post with all relationships
const post = await services.entity.findOne('post', PostModel, {
  $where: { id: postId },
  $withRelated: ['author', 'category', 'tags']
});

// Access related data
console.log(`Author: ${post.author.name}`);
console.log(`Category: ${post.category.name}`);
console.log(`Tags: ${post.tags.map(t => t.name).join(', ')}`);

// Get author with all their posts
const author = await services.entity.findOne('author', AuthorModel, {
  $where: { id: authorId },
  $withRelated: ['posts']
});

console.log(`${author.name} has ${author.posts.length} posts`);

Cascading Operations

Soft Delete with Relationships

When you delete an author, posts remain but become orphaned:

// Option 1: Prevent deletion if author has posts
export class ValidateAuthor extends CustomFunction {
  async exec(): Promise<CustomFunctionResponse> {
    const { entity, operation, services } = this.context;

    if (operation === HookOperation.delete) {
      const postCount = await services.entity.knex('posts')
        .where({ author_id: entity.id, _is_deleted: false })
        .count('* as count')
        .first();

      if (postCount.count > 0) {
        return {
          valid: false,
          errors: [{
            field: 'id',
            message: `Cannot delete author with ${postCount.count} posts`
          }]
        };
      }
    }

    return { valid: true, entity };
  }
}

// Option 2: Reassign posts to different author
export class ValidateAuthor extends CustomFunction {
  async exec(): Promise<CustomFunctionResponse> {
    const { entity, operation, services } = this.context;

    if (operation === HookOperation.delete) {
      // Reassign all posts to "Deleted User" author
      const deletedUserAuthor = await services.entity.findOne('author', {}, {
        $where: { email: 'deleted@example.com' }
      });

      if (deletedUserAuthor) {
        await services.entity.knex('posts')
          .where({ author_id: entity.id })
          .update({ author_id: deletedUserAuthor.id });
      }
    }

    return { valid: true, entity };
  }
}

Best Practices

1. Always Use $withRelated for Nested Data

// ✅ Good - Efficient single query
const posts = await services.entity.findAll('post', PostModel, {
  $where: { status: 'Published' },
  $withRelated: ['author', 'category', 'tags']
});

// ❌ Bad - N+1 queries
const posts = await services.entity.findAll('post', PostModel, {
  $where: { status: 'Published' }
});
for (const post of posts) {
  post.author = await services.entity.findOne('author', {}, {
    $where: { id: post.author_id }
  });
}

2. Index Foreign Keys

{
  "label": "Author",
  "key": "author_id",
  "type": "SingleDropDown",
  "index": true,
  "relationshipOptions": {
    "ref": "author"
  }
}

3. Use Descriptive Labels

{
  "label": "Author",
  "key": "author_id",
  "type": "SingleDropDown",
  "typeOptions": {
    "label": "{{name}} ({{email}})"
  }
}

{
  "$withRelated": [
    "posts(notDeleted)",
    "posts(notDeleted).category(active)"
  ]
}

Troubleshooting

Problem: {{author.name}} shows blank

Solution:

Add to $withRelated: ["author(notDeleted)"]
Add to $select: ["author.name"]
Check foreign key value exists

Junction Table Not Created

Problem: Many-to-Many not working

Solution:

Ensure both entities have MultiDropDown field
Restart Stack9 to trigger table creation
Check database for {entity1}_{entity2} table

Circular References

Problem: Query hangs when loading nested relationships

Solution:

{
  "$withRelated": [
    "author",
    "author.posts"
  ]
}

Avoid infinite loops - don't load author.posts.author.posts...

Summary

You now understand:

✅ One-to-Many - Parent → Children (Author → Posts) ✅ Many-to-One - Child → Parent (Post → Author) ✅ Many-to-Many - Both directions (Posts ↔ Tags) ✅ Nested Relationships - Loading related data of related data ✅ Querying - Using $withRelated and dot notation ✅ Display - Showing relationships in screens

Next Steps

Implementing Business Logic - Add validation and rules
Building Workflows - Automate actions on relationship changes
Performance Optimization - Optimize relationship queries

What You'll Learn​

Relationship Types Overview​

Example: Blog System​

Step 1: Create the Author Entity​

Step 2: Create the Category Entity​

Step 3: Create the Tag Entity​

Step 4: Create the Post Entity with All Relationships​

Step 5: Query Posts with Relationships​

Step 6: Display Relationships in List Screen​

Step 7: Author Detail with Child Posts​

Step 8: Advanced Query - Posts with All Relationships​

Relationship Patterns​

Pattern 1: One-to-Many (Parent → Children)​

Pattern 2: Many-to-One (Child → Parent)​

Pattern 3: Many-to-Many (Both Directions)​

Working with Relationships in Code​

Entity Hooks with Relationships​

Querying Relationships Programmatically​

Cascading Operations​

Soft Delete with Relationships​

Best Practices​

1. Always Use $withRelated for Nested Data​

2. Index Foreign Keys​

3. Use Descriptive Labels​

4. Apply Scopes to Related Data​

Troubleshooting​

Related Data Not Loading​

Junction Table Not Created​

Circular References​

Summary​

Next Steps​

What You'll Learn

Relationship Types Overview

Example: Blog System

Step 1: Create the Author Entity

Step 2: Create the Category Entity

Step 3: Create the Tag Entity

Step 4: Create the Post Entity with All Relationships

Step 5: Query Posts with Relationships

Step 6: Display Relationships in List Screen

Step 7: Author Detail with Child Posts

Step 8: Advanced Query - Posts with All Relationships

Relationship Patterns

Pattern 1: One-to-Many (Parent → Children)

Pattern 2: Many-to-One (Child → Parent)

Pattern 3: Many-to-Many (Both Directions)

Working with Relationships in Code

Entity Hooks with Relationships

Querying Relationships Programmatically

Cascading Operations

Soft Delete with Relationships

Best Practices

1. Always Use $withRelated for Nested Data

2. Index Foreign Keys

3. Use Descriptive Labels

4. Apply Scopes to Related Data

Troubleshooting

Related Data Not Loading

Junction Table Not Created

Circular References

Summary

Next Steps