6

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();
ronatory
  • 7,156
  • 4
  • 29
  • 49

1 Answers1

4

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.

jboot
  • 701
  • 9
  • 17
  • Thanks for your answer. It brought me close to what I want. I had to cast one of the sides in the having clause. Either `array_agg(categories.name)::text[]` or `ARRAY[:...categories]::varchar[]`. I also needed to add a `.addGroupBy('profile.id')`, because of the select of the profile. After that I received the expected users. BUT as written in the question I also want to select the categories, so I need a `.innerJoinAndSelect`, but then I need also to add a `.addGroupBy('categories.id')`. If I have now more than one category as input then I receive no results at all. Any hints regarding this? – ronatory Dec 11 '21 at 11:16
  • You’re doing the right thing by adding the `profile.id` to your group by and casting the array. Selecting and grouping by categories, however, will not work as the `array_agg` function will then yield arrays of one category per row. You could consider joining on `categories` a second time, which can be used in your select. You would probably need a different alias for the second join, though, to avoid name collisions. There are ways to do it with one join and more aggregate functions, but that would require a query with raw results. I’ll update my answer if you can get it to work. – jboot Dec 11 '21 at 19:58
  • Thanks for the hints, I already in the meantime I already updated my code to just use a string array in the profile instead of a many to many relation since I spent already quite some time on the issue and this satisfies my current app requirements. Also I want to finish something. If I encounter that issue again in the future with a needed many to many relation I will try to solve it with your hints. Will upvote your answer anyways since it brought me pretty close to the solution – ronatory Dec 13 '21 at 16:09