Use the "where" clause to filter records with empty parents

[pedramardakani]

Hello there!

I've created a Category table, containing uuid, name, self-referencing parent, and subcategory:

@Entity()
export class Category {
  @PrimaryGeneratedColumn('uuid')
  uuid: string;

  @Column({ unique: true })
  name: string;

  @ManyToOne(() => Category, (category) => category.subcategories)
  parent: Category;

  @OneToMany(() => Category, (category) => category.parent)
  subcategories: Category[];
}

I've seeded this table with several records, where some of them have no parents (i.e. a category) and some do (i.e. a subcategory). Now I'd like to return a paginated response when clients ask for the categories. The response should be like:

[{ 
  "name": "category1",
  "subcategories": [
    { "name": "subcategory1" },
    { "name": "subcategory2" },
    { "name": "subcategory3" }
  ]
},{ 
  "name": "category2",
  "subcategories": [
    { "name": "subcategory1" },
    { "name": "subcategory2" },
    { "name": "subcategory3" }
  ]
}]

I've managed to do this using createQueryBuilder like so:

@Injectable()
export class CategoryService {
  constructor(
    @InjectRepository(Category)
    protected readonly categoryRepository: Repository<Category>,
  ) {}

  static readonly PAGINATION_CONFIG: PaginateConfig<Category> = {
    sortableColumns: ['name', 'subcategories.name'],
    nullSort: 'last',
    defaultSortBy: [['name', 'DESC']],
    relations: ['subcategories', 'parent'],
    loadEagerRelations: true,
    maxLimit: 0,
    filterableColumns: {
      name: [FilterOperator.ILIKE],
      'subcategories.name': [FilterOperator.ILIKE],
    },
  };

  async findAll(query: PaginateQuery) {
    return paginate(
      query,
      this.categoryRepository
        .createQueryBuilder('bcr')
        .select()
        .where('bcr.parent IS NULL'),
      categoryService.PAGINATION_CONFIG,
    );
  }

This works fine. However, I'm wondering why using the where clause in the PAGINATION_CONFIG doesn't work:

// truncated
  static readonly PAGINATION_CONFIG: PaginateConfig<Category> = {
    where: { parent: null }, // 📌 Here I've used undefined, null, etc. How should this clause look?
    sortableColumns: ['name', 'subcategories.name'],
    searchableColumns: ['name', 'subcategories.name'],
    nullSort: 'last',
    defaultSortBy: [['name', 'DESC']],
    relations: ['subcategories'],
    maxLimit: 0,
    filterableColumns: {
      name: [FilterOperator.ILIKE],
      'subcategories.name': [FilterOperator.ILIKE],
    },
  };

  async findAll(query: PaginateQuery) {
    return paginate(
      query,
      this.categoryRepository,
      CategoryService.PAGINATION_CONFIG,
    );
  }
// truncated

Any ideas?

[pedramardakani]

Oh I just found the culprit. The where clause must use IsNull() function imported from typeorm:

// truncated
import { IsNull, Repository } from 'typeorm'; // 🫲 Import IsNull
// truncated
  static readonly PAGINATION_CONFIG: PaginateConfig<Category> = {
    where: { parent: IsNull() }, // 🫲 here's the solution
    sortableColumns: ['name', 'subcategories.name'],
    searchableColumns: ['name', 'subcategories.name'],
    nullSort: 'last',
    defaultSortBy: [['name', 'DESC']],
    relations: ['subcategories'],
    maxLimit: 0,
    filterableColumns: {
      name: [FilterOperator.ILIKE],
      'subcategories.name': [FilterOperator.ILIKE],
    },
  };

  async findAll(query: PaginateQuery) {
    return paginate(
      query,
      this.categoryRepository,
      CategoryService.PAGINATION_CONFIG,
    );
  }
// truncated

Kudos to hungneox's answer on SO.