Why Typeorm does not populate the data of joined column?

Question

I am using Typeorm, Typscript and Node to build an application to schedule and manage live events.

Before anyone start the question please read the question information carefully. It is bit long but I have tried my best to described every details.

models/event.ts

@Entity('user_events')
export class UserEvent extends NewBaseModel {
  @PrimaryGeneratedColumn({ name: 'event_id' })
  @IsNotEmpty()
  public eventId: number;

  @Column({ name: 'title', nullable: false })
  public title: string;

  @ManyToOne(type => User, user => user.events, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'user_id' })
  public addedBy: User;

  @OneToMany(() => EventMembers, member => member.event)
  public eventMembers: EventMembers[];

  @BeforeUpdate()
  public async updateDetails(): Promise<void> {
    this.updatedDate = moment().utc();
  }
}

models/user.ts

@Entity('users')
export class User extends NewBaseModel {
  @PrimaryGeneratedColumn({ name: 'user_id' })
  @IsNotEmpty()
  public userId: number;

  @IsNotEmpty()
  @Exclude()
  @Column({ name: 'password' })
  public password: string;

  @IsEmail()
  @Column({ name: 'email' })
  public email: string;

  @OneToMany(type => UserEvent, event => event.addedBy)
  public events: UserEvent[];

  @OneToMany(() => EventMembers, members => members.userId)
  public eventMembers: EventMembers[];
}

models/event-members.ts

@Entity('event_members')
export class EventMembers extends NewBaseModel {
  @PrimaryGeneratedColumn({ name: 'event_member_id' })
  @IsNotEmpty()
  public eventMemberId: number;

  @ManyToOne(type => UserEvent, event => event.eventMembers, { onDelete: 'CASCADE' })
  @JoinColumn()
  public event: UserEvent;

  @ManyToOne(type => User, user => user.eventMembers, { onDelete: 'CASCADE' })
  @JoinColumn()
  public user: User;

  @Column({ name: 'event_id' })
  public eventId: number;

  @Column({ name: 'user_id' })
  public userId: number;
  
  @BeforeInsert()
  public async hashPassword(): Promise<void> {
    this.createdDate = moment().format('YYYY-MM-DD HH:mm:ss');
  }

  @BeforeUpdate()
  public async updateDetails(): Promise<void> {
    this.updatedDate = moment().format('YYYY-MM-DD HH:mm:ss');
  }
}

event.controller.js

const event: UserEvent = await this.eventService.findOne({
    where: {
        eventId: eventParam.id,
    },
    relations: ['posters', 'eventMembers'],
});

event.service.ts

public findOne(findCondition: any): Promise<any> {
  return this.eventRepository.findOne(findCondition);
}

Above is the code already writted by other developer. Now my issue that when I comment the below code of the models/event-members.ts file. Now execute commands of drop and run migrations.

@Column({ name: 'event_id' })
public eventId: number;

@Column({ name: 'user_id' })
public userId: number;

The query is not return the user_id and event_id in the response. Following is the response contains no user_id and event_id.

UserEvent {
  uuid: 'd4ed7c0c-24f2-4cb3-8069-a8c3219bf157',
  eventId: 8,
  title: 'Christina Aguilera - Virtual Experiences',
  eventMembers: [
    EventMembers {
      createdDate: 2021-04-17T13:47:17.000Z,
      updatedDate: 2021-04-17T13:47:17.699Z,
      isDeleted: false,
      isActive: true,
      uuid: '41adc4b3-2e37-4c99-95d4-cfe4fb6b2005',
      eventMemberId: 64
    },
    EventMembers {
      createdDate: 2021-04-17T13:47:17.000Z,
      updatedDate: 2021-04-17T13:47:17.699Z,
      isDeleted: false,
      isActive: true,
      uuid: '3cde77f7-b332-4be2-94fa-a580ec729f57',
      eventMemberId: 65
    },
    EventMembers {
      createdDate: 2021-04-17T13:47:17.000Z,
      updatedDate: 2021-04-17T13:47:17.699Z,
      isDeleted: false,
      isActive: true,
      uuid: 'ab91625d-89b4-43ef-b16f-87c2f1f50b36',
      eventMemberId: 66
    }
  ]
}

Now if I uncomment the below code from the model file. Now I run command to drop and run he migrations again.

@Column({ name: 'event_id' })
public eventId: number;

@Column({ name: 'user_id' })
public userId: number;

Once I do execute above steps, and see the table it will add 2 more fields. Please look into that table

. I adds 2 more fields in the database and describe the table

Now If I call an api and execute the above described code of event.controller.ts it will respond me event_id and user_id in the api object.

UserEvent {
  uuid: 'a520ab78-1269-418d-9ecc-988bfe7dad95',
  eventId: 1,
  title: 'Christina Aguilera - Virtual Experiences',
  eventMembers: [
    EventMembers {
      createdDate: 2021-04-17T16:54:09.000Z,
      updatedDate: 2021-04-17T16:08:56.000Z,
      isDeleted: false,
      isActive: true,
      uuid: '30e835a0-babc-452b-9626-5178c05fa680',
      eventMemberId: 4,
      eventId: 1,
      userId: 3
    },
    EventMembers {
      createdDate: 2021-04-17T16:54:09.000Z,
      updatedDate: 2021-04-17T16:08:56.000Z,
      isDeleted: false,
      isActive: true,
      uuid: 'a8e2418f-94be-4c5c-af38-c284685cd051',
      eventMemberId: 5,
      eventId: 1,
      userId: 4
    },
    EventMembers {
      createdDate: 2021-04-17T16:54:09.000Z,
      updatedDate: 2021-04-17T16:08:56.000Z,
      isDeleted: false,
      isActive: true,
      uuid: '8edb8b39-5095-4595-8460-1a402cec7fdb',
      eventMemberId: 6,
      eventId: 1,
      userId: 6
    },
  ]
}

If you looking in the object the value contains now event_id and user_id display.

Now my question is that why this is being happened, Why I can't get the value of event_id and user_id fromt he join table. To access the directly event_id and user_id value from the table I should add 2 redudent column in the database and both contains the same value.

Becuase event_id column value is similar to eventEventId column and user_id column value is similar to userUserId column.

I want to remove event_id and user_id column from the table and Do anyway that I can get directly the value of event_id and user_id in the query response with typeorm find() function.

Can anyone have solutions and guideline that what should I do in this case?

Answer 1

Why you have eventEventId and userUserId columns

In typeorm, when you have a @ManyToOne relationship, if you do not specify custom parameters inside @JoinColumn, this would create a new column in the database table to keep the relationship. By default it would be propertyName + referencedColumnName. Check the documentation for more info.

Your EventMembers entity has @ManyToOne relations like below:

@Entity('event_members')
export class EventMembers extends NewBaseModel {
  ...

  @ManyToOne(type => UserEvent, event => event.eventMembers, { onDelete: 'CASCADE' })
  @JoinColumn()
  public event: UserEvent;

  @ManyToOne(type => User, user => user.eventMembers, { onDelete: 'CASCADE' })
  @JoinColumn()
  public user: User;
  
  ...
}

This means in the actual database table of event_members, there should be eventEventId and userUserId columns created as foreign key references (This is what you see at the moment as well).

---

Why your user_id is similar to userUserId

Now, if you look at User entity, you have defined the related @OneToMany relationship like below:

@Entity('users')
export class User extends NewBaseModel {
  ...

  @OneToMany(() => EventMembers, members => members.userId)
  public eventMembers: EventMembers[];
}

I believe members => members.userId part of the code is the reason you are going to see the value of userUserId to be similar to user_id. Ideally, that should have been defined as members => members.user to avoid this kind of confusion.

When you say,

Now if I uncomment the below code from the model file. Now I run command to drop and run he migrations again.

I believe you had to do some changes to the above relation because otherwise when you comment out userId property of EventMembers entity, the above relation in User entity should have a syntax error.

However, I have no idea how your event_id column value is similar to eventEventId column value.

---

Solution

Change your User entity as below:

@Entity('users')
export class User extends NewBaseModel {
  ...

  @OneToMany(() => EventMembers, members => members.user)
  public eventMembers: EventMembers[];
}

Change your EventMembers entity as below:

@Entity('event_members')
export class EventMembers extends NewBaseModel {
  ...

  @ManyToOne(type => UserEvent, event => event.eventMembers, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'eventId' })
  public event: UserEvent;

  @ManyToOne(type => User, user => user.eventMembers, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'userId' })
  public user: User;

  @Column({ name: 'eventId' })
  public eventId: number;

  @Column({ name: 'userId' })
  public userId: number;
  ...
}

Now your event_members table should only have eventId and userId columns as foreign key references.

Hope this helps you. Cheers 🍻!