Working with Relationships

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

// User model
export const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.Integer, primaryKey: true },
    name: { type: DataTypes.String, length: 255 },
    email: { type: DataTypes.String, length: 100 },
  },
  relations: [
    {
      type: RelationType.OneToMany,
      target: () => Post,
      property: "posts",
      foreignKey: "userId",
    },
  ],
});

// Post model
export const Post = defineModel({
  tableName: "posts",
  columns: {
    id: { type: DataTypes.Integer, primaryKey: true },
    userId: { type: DataTypes.Integer, required: true },
    title: { type: DataTypes.String, length: 255 },
    content: { type: DataTypes.Text },
  },
  relations: [
    {
      type: RelationType.ManyToOne,
      target: () => User,
      property: "user",
      foreignKey: "userId",
    },
  ],
});

const userRepository = orm.getRepository(User);

// Load user with their posts using JOIN
const userWithPosts = await userRepository
  .find()
  .join("posts", "users.id = posts.userId")
  .where("users.id = ?", 1)
  .execute(client);

console.log(userWithPosts[0].name);
// To get posts, you may need to manually group or join results

// Load all users with their post counts
const usersWithPosts = await userRepository
  .find()
  .join("posts", "users.id = posts.userId")
  .select("users.id", "users.name", "COUNT(posts.id) as post_count")
  .groupBy("users.id", "users.name")
  .execute(client);

usersWithPosts.forEach(user => {
  console.log(`${user.name} has ${user.post_count} posts`);
});

export const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.Integer, primaryKey: true },
    name: { type: DataTypes.String, length: 255 },
  },
  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.Integer, primaryKey: true },
    name: { type: DataTypes.String, length: 50 },
  },
  relations: [
    {
      type: RelationType.ManyToMany,
      target: () => User,
      property: "users",
      joinTable: "user_roles",
      foreignKey: "roleId",
      inverseKey: "userId",
    },
  ],
});

// Load user with roles (JOIN)
const userWithRoles = await userRepository
  .find()
  .join("user_roles", "users.id = user_roles.userId")
  .join("roles", "user_roles.roleId = roles.id")
  .where("users.id = ?", 1)
  .select("users.*", "roles.name as role_name")
  .execute(client);

userWithRoles.forEach(row => {
  console.log(`${row.name} has role: ${row.role_name}`);
});

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

console.log(`Found ${admins.length} admins`);

// Load user with posts and each post's comments using JOINs
const usersWithPostsAndComments = await userRepository
  .find()
  .join("posts", "users.id = posts.userId")
  .join("comments", "posts.id = comments.postId")
  .where("users.id = ?", 1)
  .select("users.*", "posts.title as post_title", "comments.content as comment_content")
  .execute(client);

// Group results in code as needed:
usersWithPostsAndComments.forEach(row => {
  console.log(`${row.name} wrote post: ${row.post_title}`);
  console.log(`Comment: ${row.comment_content}`);
});