How can I define many to many columns with NestJS and TypeORM?

Question

I'm new to NestJS/TypeORM so apologies and forgiveness please.

I have a many to many relationship created; my table is created automatically with the correct columns.

I have a location that can have many users, and a user can have many locations.

My route looks like this:

http://localhost:3000/locations/:location-id/users

My location.entity.ts looks like this:

@ManyToMany(type => User, user => user.locations, { eager: true })
  @JoinTable()
  users: User[];

My user.entity.ts looks like this:

@ManyToMany(type => Location, location => location.users, { eager: false })
  locations: Location[];

location_users_user table is getting generated with these columns:

locationId | userId

So far, everything looks great! When I send a GET request to my route using Postman, I am seeing this error in the console:

column location_users_user.locationid does not exist 

I see that locationid is what it's looking for, when my column name is locationId. Is there somewhere I need to set the case of the column names?

I have also worked through this SO thread to set additional params in the JoinTable decorator.

That leaves me with this:

// location.entitiy.ts
@ManyToMany(type => User, user => user.locations, { eager: true })
  @JoinTable({
    name: 'location_user',
    joinColumn: {
      name: 'locationId',
      referencedColumnName: 'id',
    },
    inverseJoinColumn: {
      name: 'userId',
      referencedColumnName: 'id',
    },
  })
  users: User[];

However, I'm still getting this error:

column location_users_user.locationid does not exist 

I don't think I'm setting the correct Join or something. I only have that decorator on my location entity.

Thank you for any suggestions!

EDIT

I have updated my user.repository.ts file as follows:

async getLocationUsers(locationId: number): Promise<User[]> {
    const query = this.createQueryBuilder('location_user')
      .where('location_user.locationId = :locationId', { locationId });

The error still thinks I am looking for a locationid column. I've changed it to foo to just see if I was even in the correct spot and I am. I'm not sure why it's missing the case of locationId.

EDIT2

I've found that it could be a possible Postgres thing? Using double quotes, I'm now seeing the correct table/column name in my error:

const query = this.createQueryBuilder('location_user')
      .where('location_user."locationId" = :locationId', { locationId });

Results in: column location_user.locationId does not exist Which is still odd, because that table does exist and so does the column.

Edit

Here is the location.entity.ts file:

@PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @ManyToMany(type => User, user => user.locations, { eager: true })
  @JoinTable()
  users: User[];

Here is the user.entity.ts file:

 @PrimaryGeneratedColumn()
 id: number;

 @Column()
 email: string;

 @ManyToMany(type => Location, location => location.users, { eager: false })
 locations: Location[];

I'm able to see the users' relationship when I get a specific location, so I know that's working properly. I am trying to just get all users that belong to the location; here is what my user.repository.ts file looks like:

async getLocationUsers(locationId: number): Promise<User[]> {
    const query = this.createQueryBuilder('user')
      .where('location_users_user."locationId" = :locationId', { locationId });
});

    try {
      return await query.getMany();
    } catch (e) {
      console.log('error: ', e);
    }
  }

Answer 1

I've just checked some of my own code using @ManyToMany, the only 2 differences I can spot are the following:

1st Best bet

In my repository, when calling TypeOrm's createQueryBuilder method with a joined column, two things differ:

1. I use a leftJoin (might be another one depending on your use case logic and DB design) to retrieve the linked table
2. There is no double quote on the FK field you want to filter the results

Your getLocationUsers method's code in user.repository.ts would result in the following:

async getLocationUsers(locationId: number): Promise<User[]> {
    const query = this.createQueryBuilder('user')
      .leftJoin('user.locations', 'location')
      .where('location.id = :locationId', { locationId });
});

    try {
      return await query.getMany();
    } catch (e) {
      console.log('error: ', e);
    }
  }

2nd just in case 1st one doesn't solve your problem

@ManyToMany(type => Location, location => location.users, { eager: false })

while I'm using

@ManyToMany(() => Location, (location) => location.users, { eager: false })

Would this difference (don't use type => but () =>) change anything ? I honestly don't think so, might be some syntax sugar (or not - to be confirmed, this is only assumption)

Hope it helps, let me know :)