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.
how to order by postCount?
You can see this one: TypeORM: Sort Results by a Relation Column
Hi, also interested how to order by postCount column
Hi, ‘heithem’ asked how to order by the mapped column, not a relational one that is covered in your linked post.