TypeORM: Find Records by Relation Columns

Updated: September 27, 2022 By: Snowball Post a comment

In TypeORM, you can select rows in a table based on the relation columns (in the related table) by using a query builder like this:

const postRepository = myDataSource.getRepository(Post);

const posts = await postRepository
      .createQueryBuilder('post')
      .innerJoinAndSelect('post.user', 'user')
      .where('user.age > :age', { age: 45 })
      .getMany();

console.log(posts);

The code snippet above retrieves all posts that were created by any user whose age is greater than 45.

For more clearness, you can see the User entity and the Post entity below.

User entity:

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

@Entity({ name: 'users' })
@Unique(['email'])
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  @Column({ nullable: true })
  age: number;

  @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';

@Entity({name: 'posts'})
export class Post {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne((type) => User, (user) => user.posts, { cascade: true })
  user: User;

  @Column()
  title: string;

  @Column()
  body: string;
}