TypeORM: Counting Records in One-To-Many Relation

Last updated on September 4, 2022 Pennywise Loading... 4 comments

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:

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

Subscribe
Notify of
guest
4 Comments
Inline Feedbacks
View all comments
heithem
heithem
3 months ago

how to order by postCount?

A Goodman
Admin
A Goodman
3 months ago
Reply to  heithem
Nivolves
Nivolves
2 months ago
Reply to  A Goodman

Hi, also interested how to order by postCount column

Auridas
Auridas
3 months ago
Reply to  A Goodman

Hi, ‘heithem’ asked how to order by the mapped column, not a relational one that is covered in your linked post.

You May Also Like