Working with Relationships

import { defineModel, DataTypes, RelationType } from "stabilize-orm";

export const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    name: { type: DataTypes.STRING, length: 100 },
    email: { type: DataTypes.STRING, length: 255, required: true, unique: true },
  },
  relations: [
    {
      type: RelationType.OneToMany,
      target: () => Post,
      property: "posts",
      foreignKey: "authorId",
    },
  ],
});

export const Post = defineModel({
  tableName: "posts",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    title: { type: DataTypes.STRING, length: 200, required: true },
    body: { type: DataTypes.TEXT },
    authorId: { type: DataTypes.STRING, required: true },
  },
  relations: [
    {
      type: RelationType.ManyToOne,
      target: () => User,
      property: "author",
      foreignKey: "authorId",
    },
  ],
});

const postRepo = orm.getRepository(Post);

// Find post with author loaded via JOIN
const postsWithAuthor = await postRepo
  .find()
  .join("users", "posts.authorId = users.id")
  .select("posts.*", "users.name as authorName")
  .execute(orm.client);

// Count posts per user
const userPostCounts = await userRepo
  .find()
  .leftJoin("posts", "users.id = posts.authorId")
  .select("users.name", "COUNT(posts.id) as postCount")
  .groupBy("users.id", "users.name")
  .execute(orm.client);

export const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    name: { type: DataTypes.STRING, length: 100 },
  },
  relations: [
    {
      type: RelationType.ManyToMany,
      target: () => Role,
      property: "roles",
      joinTable: "user_roles",
      foreignKey: "userId",
      inverseKey: "roleId",
    },
  ],
});

export const Role = defineModel({
  tableName: "roles",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    name: { type: DataTypes.STRING, length: 50, required: true },
  },
  relations: [
    {
      type: RelationType.ManyToMany,
      target: () => User,
      property: "users",
      joinTable: "user_roles",
      foreignKey: "roleId",
      inverseKey: "userId",
    },
  ],
});

// Find users with their roles
const usersWithRoles = await userRepo
  .find()
  .join("user_roles", "users.id = user_roles.userId")
  .join("roles", "user_roles.roleId = roles.id")
  .select("users.*", "roles.name as roleName")
  .execute(orm.client);

// Find all admin users
const admins = await userRepo
  .find()
  .join("user_roles", "users.id = user_roles.userId")
  .join("roles", "user_roles.roleId = roles.id")
  .where("roles.name = ?", "Admin")
  .execute(orm.client);