TypeORM: How to query ManyToMany relation with string array input to find entities where all strings should exist in the related entity's column?

Question

I have a User entity which has a OneToOne relation to a Profile entity and the Profile entity has a ManyToMany relation to a Category entity.

// user.entity.ts

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

  @OneToOne(() => Profile, {
    cascade: true,
    nullable: true,
  })
  @JoinColumn() // user owns the relationship (User table contains profileId). Use it only on one side of the relationship
  profile: Profile;
}

// profile.entity.ts

@Entity()
export class Profile {
  @PrimaryGeneratedColumn('uuid')
  id: number;

  @OneToOne(() => User, (user: User) => user.profile)
  user: User;

  @ManyToMany(() => Category, (category: Category) => category, {
    cascade: true,
    nullable: true,
  })
  @JoinTable()
  categories: Category[];
}

// category.entity.ts

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

  @Column()
  name: string;

  @ManyToMany(() => Profile, (profile: Profile) => profile.categories, {
    nullable: true,
  })
  profiles: Profile[];
}

My goal is to get all user entities where category names of the profile are all present in a string array as input e.g. const categories = ['category1', 'category2']. So far using IN with a query builder brings me close to my goal.

This is the query with IN:

const categories = ['category1', 'category2']

const users = await this.usersRepository
  .createQueryBuilder('user')
  .innerJoinAndSelect('user.profile', 'profile')
  .innerJoinAndSelect('profile.categories', 'categories')
  .where('categories.name IN (:...categories)', {
    categories,
  })
  .getMany();

I want only users where category1 AND category2 are present as names of many to many relations of the profile. With the query above I receive also users where only one of these values are present as names. Is this even possible with my current structure?

This comes close to mine, but there the OP has unrelated entities.

This comes also close, but there it's just a string array column for the filtering.

Also I would like to keep my current structure, because may want to add some other columns to the category entity like an order for example.

Update:

I decided to use a string array instead of a many to many relation since it satisfies my own requirements.

// profile.entity.ts

@Column('text', {
  nullable: true,
  array: true,
})
categories?: string[];

The updated query:

const categories = ['category1', 'category2']

const users = await this.usersRepository
  .createQueryBuilder('user')
  .innerJoinAndSelect('user.profile', 'profile')
  .where('profile.categories::text[] @> (:categories)::text[]', {
    categories,
  })
  .getMany();

Answer 1

If you're using PostgreSQL, you could use the @> contains array operator.

const categories = ['category1', 'category2']

// untested code
const users = await this.usersRepository
  .createQueryBuilder('user')
  .innerJoinAndSelect('user.profile', 'profile')
  .innerJoin('profile.categories', 'categories')
  .groupBy('user.id')
  .addGroupBy('profile.id');
  .having('array_agg(categories.name::text) @> ARRAY[:...categories]', {
    categories,
  })
  .getMany();

Instead of selecting the categories, it just aggregates the joined categories into an array and checks whether it's a superset of the given array or not. I wasn't able to test this with TypeORM, so I'm just hoping it can deal with the array-building syntax as I couldn't find it anywhere in the documentation. I hope you find this solution helpful.

Edit: Added missing groupBy and missing cast, as mentioned in the comments.