TypeORM, ManyToOne relation: get parents rows that have no child relations

Question

I have 2 tables, lists and items. A list can have 0 or many items. An item is only in one list.

export class List {
  @OneToMany(() => Item, (item) => item.list, {
    nullable: true,
  })
  items: Item[];
}

export class Item {
  @ManyToOne(() => List, (list) => list.items)
  list: List;
}

• How can I get all the list objects that have 0 item?

---

My code below is returning an error: Unknown column 'list.items' in 'where clause'.

const listsWithoutItems = await this.listsRepository
  .createQueryBuilder('list')
  .where('list.item IS NULL')
  .getMany();

Answer 1

The reason for your error is that you are selecting only 'list' in your query, you have not included 'list.items'.

One way you can get only the 'list' records without 'items' is to specifically add this to the .where

const listsWithoutItems = await this.listsRepository
.createQueryBuilder('list')
.where('NOT EXISTS (SELECT * FROM Item i WHERE i.listId = list.id)')
.getMany();

Another way is to do a left-join from 'list' to 'item', selecting only those with NULL 'Item.Id'

const listsWithoutItems = await listsRepository
    .createQueryBuilder('list')
    .leftJoin('list.items', 'Item')
    .where('Item.id IS NULL')
    .getMany();

(You may need to switch on TypeOrm Query Logging to see the generated SQL and get these exactly right, especially if your database is case-sensitive).