Joining two tables — Users & Posts

[rstorms]

Hey guys, I have a table of multiple users on a postgreSQL DB. I am using type-graphql and typeorm. This is the model of the table:

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  BaseEntity,
  Unique,
} from 'typeorm';
import { ObjectType, Field, Int } from 'type-graphql';

@ObjectType()
@Entity('users')
@Unique(['username'])
@Unique(['email'])
export class User extends BaseEntity {
  @Field(() => Int)
  @PrimaryGeneratedColumn()
  id: number;

  @Field()
  @Column('text')
  email: string;

  @Field()
  @Column('text')
  username: string;

  @Field()
  @Column('text', { default: 'user' })
  role: string;

  @Column('text')
  password: string;

  @Column('int', { default: 0 })
  tokenVersion: number;

  @CreateDateColumn()
  createdAt: string;

  @UpdateDateColumn({ type: 'timestamp' })
  updatedAt: number;
}

Everything works as expected. Now, I am trying to make a table for ‘posts’ and connect each post for each user, however I’m unsure as to what I need to do here. Do I use the User ID as the primary key for the posts?

This is what I have so far:

import {
  Entity,
  PrimaryGeneratedColumn,
  CreateDateColumn,
  UpdateDateColumn,
  BaseEntity,
  Column,
} from 'typeorm';
import { ObjectType, Field, Int } from 'type-graphql';

@ObjectType()
@Entity('posts')
export class Post extends BaseEntity {
  @Field(() => Int)
  @PrimaryGeneratedColumn()
  id: number;

  @Field()
  @Column('text')
  post: string;

  @CreateDateColumn()
  createdAt: string;

  @UpdateDateColumn({ type: 'timestamp' })
  updatedAt: number;
}

Backend is still not one of my strengths and I definitely get confused on joining. Any help is appreciated.

[tluolamo]

No, you don’t use user id as primary key in posts. You would use a unique id for that. You can use your user id as a foreign key or basically a secondary indexed field and then you join based on that. In your schema you seem to have the id field already correctly in the posts table, but it seems to be missing the user id

[rstorms]

Ahh, sorry, foreign key is what I meant! Is this kinda what you mean? I found these methods from typeorm:

import {
  Entity,
  PrimaryGeneratedColumn,
  CreateDateColumn,
  UpdateDateColumn,
  BaseEntity,
  Column,
  JoinColumn,
  OneToOne,
} from 'typeorm';
import { ObjectType, Field, Int } from 'type-graphql';
import { User } from './User';

@ObjectType()
@Entity('posts')
export class Post extends BaseEntity {
  @Field(() => Int)
  @PrimaryGeneratedColumn()
  id: number;

  @Field()
  @Column('text')
  post: string;

  @CreateDateColumn()
  createdAt: string;

  @UpdateDateColumn({ type: 'timestamp' })
  updatedAt: number;

  @OneToOne(() => User)
  @JoinColumn({ name: 'UserId' })
  user: User;
}

I still need to write the code to resolve this entity, but thanks a lot for this!

[rstorms]

There is also a foreign key method from typeorm as well if this doesn’t seem to work, but I see this in pgAdmin:

[tluolamo]

So you probably want to use many to one relationship as described in the link. As there is one user who has many posts. But otherwise I think you are on the right track now.

[rstorms]

I definitely need to take a break as I’m not able to solve it right now, but this is what I have working. I’m able to get back posts with just a title and description. I can get back the userId when making a post but my routes just might be wrong.

User Model:

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  BaseEntity,
  Unique,
  OneToMany,
} from 'typeorm';
import { ObjectType, Field, Int } from 'type-graphql';
import { Post } from './Post';

@ObjectType()
@Entity('users')
@Unique(['username'])
@Unique(['email'])
export class User extends BaseEntity {
  @Field(() => Int)
  @PrimaryGeneratedColumn()
  id: number;

  @Field()
  @Column('text')
  email: string;

  @Field()
  @Column('text')
  username: string;

  @Field()
  @Column('text', { default: 'user' })
  role: string;

  @Column('text')
  password: string;

  @Column('int', { default: 0 })
  tokenVersion: number;

  @CreateDateColumn()
  createdAt: string;

  @UpdateDateColumn({ type: 'timestamp' })
  updatedAt: number;

  @Field(() => [Post])
  @Column('int', { nullable: true })
  @OneToMany(() => Post, (post) => post.user)
  posts: Post[];
}

Post Model:

import {
  Entity,
  PrimaryGeneratedColumn,
  BaseEntity,
  Column,
  ManyToOne,
} from 'typeorm';
import { ObjectType, Field, Int } from 'type-graphql';
import { User } from './User';

@ObjectType()
@Entity('posts')
export class Post extends BaseEntity {
  @Field(() => Int)
  @PrimaryGeneratedColumn()
  id: number;

  @Field()
  @Column('text')
  title: string;

  @Field()
  @Column('text')
  description: string;

  @Field(() => [User])
  @Column('int', { nullable: true })
  @ManyToOne(() => User, (user) => user.posts)
  user: User;
}

Post Resolver:

import {
  Resolver,
  Query,
  Mutation,
  UseMiddleware,
  Arg,
  Ctx,
} from 'type-graphql';
import { Post } from '../entity/Post';
import { isAuth } from '../isAuth';
import { MyContext } from '../MyContext';

// Post Resolver
@Resolver()
export class PostResolver {
  // Query for all posts
  @Query(() => [Post])
  async posts() {
    // Grab all posts
    let posts = await Post.find();

    return posts;
  }

  @Mutation(() => Boolean)
  @UseMiddleware(isAuth)
  async createPost(
    @Arg('title') title: string,
    @Arg('description') description: string,
    @Ctx() context: MyContext
  ) {
    try {
      await Post.insert({
        title,
        description,
      });

      return true;
    } catch (err) {
      console.log(err);
      return false;
    }
  }
}

I have no idea where to go from here.

[rstorms]

Figured it out! Thanks a lot! I had to use relations

  // Query for all users
  @Query(() => [User])
  @UseMiddleware(isAuth)
  async users(@Args() { role, startIndex, endIndex }: GetUsersArgs) {
    // Grab all users
    let users = await User.find({ relations: ['posts'] });

    if (role !== 'admin') {
      throw new Error('Unauthenticated');
    } else {
      // Paginate
      return users.slice(startIndex, endIndex);
    }
  }

  // Query for all posts by user
  @Query(() => [Post])
  async posts(@Arg('userId') userId: number) {
    // Grab all posts
    let posts = await Post.find({
      relations: ['user'],
      where: {
        user: {
          id: userId,
        },
      },
    });

    return posts;
  }