In my application, I have a User entity that is related to a Profile entity in a OneToOne relationship, and the Profile entity has a ManyToMany relationship with 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[];
}
I am aiming to retrieve all user entities where the category names associated with the profile match those present in a given string array input e.g.
const categories = ['category1', 'category2']The query implementation utilizing 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 specifically want users where both category1 AND category2 are among the names of the many to many relations of the profile. The current query also retrieves users if only one of these values is present in the names. Is there a way to achieve this within my existing structure?
A similar issue was discussed in this thread, but the entities were unrelated in that case.
Another relevant discussion can be found in this post, although it pertains to filtering based on a string array column rather than many to many relations.
I prefer to maintain my current structure as I might need to introduce additional columns to the category entity in the future, such as an ordering attribute.
Update:
To address my requirements more effectively, I have opted to use a string array instead of a many to many relation.
// profile.entity.ts
@Column('text', {
nullable: true,
array: true,
})
categories?: string[];
The revised query looks like this:
const categories = ['category1', 'category2']
const users = await this.usersRepository
.createQueryBuilder('user')
.innerJoinAndSelect('user.profile', 'profile')
.where('profile.categories::text[] @> (:categories)::text[]', {
categories,
})
.getMany();