TypeORM: Counting Records in One-To-Many Relation

When communicating with the database through TypeORM, there will be many cases where you need to count records in a one-to-many relationship, such as:

• You want to list the users and the number of photos belonging to each person
• You want to list the posts with the number of comments on each post

With the powerful query builder, you can do it in just a few lines of code. The key is the query method loadRelationCountAndMap(). It counts the number of entities of an entity’s relation and maps the value into some entity’s property. Optionally, you can add conditions and parameters used in the condition. These words might seems vague. Let’s examine a concrete example below.

The Example

In this example, we create 2 tables: user and post (for a minimal blog). A user can have many posts but each post belongs to only one user.

User entity:

// KindaCode.com
// User entity
import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from 'typeorm';
import { Post } from './Post';

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

  @Column()
  name: string;

  @OneToMany(type => Post, post => post.user)
  posts: Post[];
}

Post entity:

// KindaCode.com Example
// Post entity
import { Entity, Column, PrimaryGeneratedColumn, ManyToOne } from 'typeorm';
import { User } from './User';

@Entity()
export class Post {
    @PrimaryGeneratedColumn()
    id: number;

    @ManyToOne(type => User, user => user.posts)
    user: User;
    
    @Column()
    title: string;
    
    @Column()
    body: string;
}

Here’s how we retrieve all users with the number of posts of each user:

const userRepository = myDataSource.getRepository(User);
const uwersWithPostCount = await userRepository
      .createQueryBuilder('user')
      .leftJoinAndSelect('user.posts', 'post')
      .loadRelationCountAndMap('user.postCount', 'user.posts') // count posts for each user
      .getMany();

console.log('Users with post count: ', uwersWithPostCount);

You will get an output similar to this:

Users with post count:  [
  User {
    id: 1,
    name: 'Hello Update',
    posts: [ [Post], [Post] ],
    postCount: 2
  },
  User { id: 2, name: 'Goodbye Update', posts: [], postCount: 0 }
]

I hope this helps. Further reading:

• Aggregation Operations in TypeORM (Sum, Avg, Min, Max, Count)
• TypeORM: AND & OR operators
• TypeORM: Select the Most Recent Record (2 Examples)
• How to Store JSON Object with TypeORM
• TypeORM: How to Limit Query Execution Time
• TypeORM: Adding Fields with Nullable/Default Data

You can also check out our database topic page for the latest tutorials and examples.