How to filter and count relation items in typeorm?

Question

I have got an event notification service in my project. The entities look like this.

@Entity('event')
class Event {
    @Column()
    message: string;

    @Column()
    readAt: Date;

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

@Entity('user')
class User {
    @PrimaryGeneratedColumn()
    id: string;

    @Column()
    email: string;

    @Column()
    fullName: string;

    @OneToMany(() => Event, event => event.user)
    events: Event[];

    unread: number;
}

The classes, User and Event, are entities with one-to-many relations as you can see in the entity declaration and are working properly with the PostgreSQL database.

I have to find all the user objects with its unread events count.

For now, I am achieving this by finding all the users with its events and counting unread events manually with the readAt field in the event entity.

Like this:

...
const users = await this.userRepository.find({ relations: ['events'] });

users.forEach(user => {
   user.unread = user.events.reduce((cnt, event) => {
      if (event.readAt) { return cnt + 1; }
      return cnt;
   }, 0);
);
...

I know this is a bad practice and I am pretty sure that I can achieve this with the TypeOrm features, not with manual counting.

The only thing I can do is filtering unread messages in the user objects.

...
constructor(@InjectRepository(User) private userRepository: Repository<User>) { }
...
const found = await this.usersRepository.createQueryBuilder('user')
   .leftJoinAndSelect('user.events', 'events')
   .where('events.readAt is null')
   .getMany();
...

But this also has its limitation. The user objects without any unread events are not returned by this query.

Any help would be appreciated.

The app is made with the NestJs framework and all the code is written in typescript.

Thank you in advance.

Solution

Try:

const found = await this.usersRepository.createQueryBuilder('user')
   .loadRelationCountAndMap('user.unreadEventCount', 'user.events', 'event', (qb) => qb.where('event.readAt IS NULL'))
   .getMany();

About loadRelationCountAndMap:

1st argument is the property name the count would be mapped to - that would be a field on your entity that is NOT a @Column.

2nd is the relation name

3rd is the alias to use with the query

4th is the optional QueryBuilder to filter further, in your case it's checking if the readAt column is null