nestjs - How to make Inner Join to work on TypeORM?

Question

I'm trying to build a simple query on TypeORM but I'm not getting the entire data using INNER JOIN. What am I doing wrong?

The SQL query runs perfectly but the typeorm one just returns me the data for the 'watcher' table.

SQL Query

SELECT *
FROM watcher w
INNER JOIN user
ON w.userId = user.id;

TypeORM

async getSystemWideWatchers(): Promise<any[]> {
    const query = this.createQueryBuilder('watcher');
    const result = await query.innerJoin('user', 'u', 'watcher.userId = u.id').getMany();
    console.log(result)
    return result;
}

Answer 1

TypeORM has a method called innerJoinAndSelect. You use plain innerJoin. That is why user table is not selected from.

Once you change that part to innerJoinAndSelect, watch table will be selected from. However, getMany and getOne returns objects with your Entity type. Therefore, if your Entity types do not have the relationships between User and Watcher tables, selected columns will not be included in the returned object.

Before I show you how to add these relations, I want to mention that you have the option to use getRawMany function to get all selected columns, but I don't recommend using it, since relationships are much tidier (no raw column names, you get arrays of Entities corresponding to relationships) and in your case, there is no reason not to use relationships.

Now, the way I understand your database design, you have Users, and Users have Watchers. A Watcher watches only one User, and there may be multiple Watchers that watch the same user.

In this case, the relationship of User to Watcher is called "One to Many".

The relationship of Watcher to User is called "Many to One".

You need to specify this information in your Entity. Here is an example. Notice the OneToMany decorator.

@Entity()
export class User {
    @PrimaryColumn()
    id: number;

    @Column()
    userName: string;

    @OneToMany(type => Watcher, watcher => watcher.user)
    watchers: Watcher[];
}

Here is the corresponding Watcher Entity:

@Entity()
export class Watcher {
    @PrimaryColumn()
    id: number;

    @Column()
    watcherName: string;

    // we can omit this (and the join condition), if userId is a foreign key
    @Column()
    userId: number;

    @ManyToOne(type => User, user => user.watchers)
    user: User;
}

Once you have these relationships, you can select a User, along with all their Watchers, or vice versa (select a Watcher with the User they watch).

Here is how you do both:

// Select a user and all their watchers
const query = createQueryBuilder('user', 'u')
    .innerJoinAndSelect('u.watchers', 'w'); // 'w.userId = u.id' may be omitted
const result = await query.getMany();

(If you want to include users with no watchers, you use leftJoin instead of innerJoin, as you probably know.)

This is probably what you were initially trying to do:

// Select a watcher and the user they watch
const query = createQueryBuilder('watcher', 'w')
    .innerJoinAndSelect('w.user', 'u'); // 'w.userId = u.id' may be omitted
const result = await query.getMany();