Find records where its @OneToMany relation is not empty

Question

I'd like to select all Users where its photos property is not empty. In other words, "select users where photos is not empty".

import { Entity, PrimaryGeneratedColumn, Column, ManyToOne } from "typeorm"
import { User } from "./User"
​
@Entity()
export class Photo {
    @PrimaryGeneratedColumn()
    id: number
​
    @Column()
    url: string
​
    @ManyToOne(() => User, (user) => user.photos)
    user: User
}


import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from "typeorm"
import { Photo } from "./Photo"
​
@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number
​
    @Column()
    name: string
​
    @OneToMany(() => Photo, (photo) => photo.user)
    photos: Photo[]
}

Is this possible in typeorm, or is SQL the only way to go? Thank you.

Answer 1

You can use QueryBuilder and leftJoinAndSelect. The query would be as simple as:

const users = await userRepository.createQueryBuilder('user')
    .leftJoinAndSelect('user.photos', 'photos')
    .where('photos.id IS NOT NULL')
    .getRawMany();

Edit: If you want the output to contain an array of photos for each user, just replace getRawMany with getMany.

const users = await userRepository.createQueryBuilder('user')
    .leftJoinAndSelect('user.photos', 'photos')
    .where('photos.id IS NOT NULL')
    .getMany();