TypeOrm find rows based on relation with another entity

Question

I have a PersonEntity, and a Person can have many Items, some of those Items can be marked as ‘deluxe’ (deluxe property on ItemEntity). How can I get only the Persons that have all of their Items deluxe ?

@Entity({name: 'persons'})
export class PersonEntity {
    @Column({nullable: false})
    name: string;

    /** Relationships **/
    @OneToMany(type => ItemEntity, item => item.person)
    items: ItemEntity[];
}

@Entity({name: 'items'})
export class ItemEntity {
    @Column({nullable: false})
    name: string;

    @Column({nullable: false})
    deluxe: boolean;

    @Column({nullable: false})
    person_id: number;

    /** Relationships **/
    @ManyToOne(type => PersonEntity, person => person.items)
    @JoinColumn({name: 'person_id'})
    person: PersonEntity;
}

Answer 1

You can achieve it like that (using EntityManager or Person repository) :

// example using Person repository

const persons = await personRepository.find({
  join: {
    alias: 'p',
    innerJoin: {
       items: 'p.items'
    }
  },
  where: (qb) => {
    qb.where(
      `NOT EXISTS (
        SELECT * FROM items i
        WHERE p.id = i.person_id 
        AND i.deluxe = 0
      )`,
    );
  }
});