TypeORM: [364] ERROR: missing FROM-clause entry for table "userorganisation" at character 401

I just hit a pretty interesting problem when using TypeORM and joining tables that I've set up for a Postgres database. I figured it out but thought I'd post the info here for anyone else that had a similar problem.

I have 3 tables set up on my database: user, organisation, user_organisation.

The idea for this is that a user can belong to many organisations and the table called user_organisation maps users to these organisations. So my entities look like this,

user.entity.ts

import { TimestampedEntity } from '@shared/entities/timestamped.entity';
import { Organisation } from '@user/entities/organisation.entity';
import { UserOrganisation } from '@user/entities/user-organisation.entity';
import { Column, Entity, Index, JoinTable, ManyToMany, OneToMany, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
@Index(['email', 'password'])
export class User extends TimestampedEntity {
    @PrimaryGeneratedColumn()
    userId: number;

    @Column({
        length: 64
    })
    username: string;

    @Column({
        length: 500
    })
    email: string;

    @Column({
        length: 255
    })
    password: string;

    @Column({
        type: 'json',
    })
    details: any;

    @Column({
        nullable: true
    })
    refreshToken: string;

    @OneToMany(type => UserOrganisation, userOrganisation => userOrganisation.user)
    @JoinTable()
    userOrganisations: UserOrganisation[];
}

user-organisation.entity.ts

import { Organisation } from '@user/entities/organisation.entity';
import { User } from '@user/entities/user.entity';
import { Column, Entity, JoinColumn, ManyToOne, OneToOne, PrimaryColumn, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class UserOrganisation {
    @ManyToOne(type => User, user => user.userOrganisations, { primary: true })
    user: User;

    @ManyToOne(type => Organisation, organisation => organisation.userOrganisations, { primary: true })
    organisation: Organisation;
}

organisation.entity.ts

import { TimestampedEntity } from '@shared/entities/timestamped.entity';
import { UserOrganisation } from '@user/entities/user-organisation.entity';
import { User } from '@user/entities/user.entity';
import { Column, Entity, JoinColumn, JoinTable, ManyToMany, ManyToOne, OneToMany, OneToOne, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Organisation extends TimestampedEntity {
    @PrimaryGeneratedColumn()
    orgId: number;

    @Column({
        length: 255
    })
    name: string;

    @Column({
        type: 'json'
    })
    details: any;

    @OneToMany(type => UserOrganisation, userOrganisation => userOrganisation.organisation)
    userOrganisations: UserOrganisation[];
}

I was then trying to run the following query,

this.userRepository.createQueryBuilder('user')
    .where('user.email = :email', { email })
    .innerJoin(UserOrganisation, 'userOrganisation', 'user.userId = userOrganisation.userUserId')
    .getOne();

And this is the error message I got,

ERROR:  missing FROM-clause entry for table "userorganisation" at character 401

The final query printed out like this,

SELECT "user"."createdAt" AS "user_createdAt"
 , "user"."updatedAt" AS "user_updatedAt"
 , "user"."userId" AS "user_userId"
 , "user"."username" AS "user_username"
 , "user"."email" AS "user_email"
 , "user"."password" AS "user_password"
 , "user"."details" AS "user_details"
 , "user"."refreshToken" AS "user_refreshToken" 
 FROM "user" "user" 
 INNER JOIN "user_organisation" "userOrganisation" 
      ON      "user"."userId" = userOrganisation.userUserId 
 WHERE "user"."email" = $1

The way I fixed it is described below.

Solution 1:

What I noticed in the query,

SELECT "user"."createdAt" AS "user_createdAt", "user"."updatedAt" AS "user_updatedAt", "user"."userId" AS "user_userId", "user"."username" AS "user_username", "user"."email" AS "user_email", "user"."password" AS "user_password", "user"."details" AS "user_details", "user"."refreshToken" AS "user_refreshToken" FROM "user" "user" INNER JOIN "user_organisation" "userOrganisation" ON "user"."userId" = userOrganisation.userUserId WHERE "user"."email" = $1

Was that there was a difference between the user table and the userOrganisation table in the join criteria,

"user"."userId" = userOrganisation.userUserId

The user table was automatically wrapped in quotation marks but userOrganisation was not... So I changed my code to the following,

this.userRepository.createQueryBuilder('user')
        .where('user.email = :email', { email })
        .innerJoin(UserOrganisation, 'userOrganisation', '"user"."userId" = "userOrganisation"."userUserId"')
        .getOne();

If you look above, I've added the quotation marks in the join criteria. It's all working now :)

Hope this helps!

Solution 2:

Well done. However when using joins in typeorm you have to write the conditions as they are defined then setting objects.

this.userRepository.createQueryBuilder('user')
        .where('user.email = :email', { email })
        .innerJoin(UserOrganisation, 'userOrganisation', 'user.userId=userOrganisation.user.userId')
        .getOne();

With this you do not need to add quotation marks.

The generated sql is not showing the quotes because it does not know how to populate the conditions.

I hope this helps.