How to select rows that are NOT IN many-to-many relation (TypeORM/PostgreSQL)

Question

I have the following entities:

export class CommonBaseEntity {
    /** Auto generated uuid v4 */
    @PrimaryGeneratedColumn('uuid')
    id: string
}

Banner templates

@Entity('banner_templates')
export class BannerTemplateEntity extends CommonBaseEntity {
    @Column()
    title: string

    /**
     * List of users for whom this banner is hidden.
     *
     * @type {UserEntity[]}
     */
    @ManyToMany(() => UserEntity, (user) => user.hiddenBanners)
    hiddenForUsers: UserEntity[]
}

Users for whom some banners were hidden

@Entity('users')
export class UserEntity extends CommonBaseEntity {
    /**
     * Unique user identifier that may refer to a user in a third-party system.
     *
     * @type {string}
     */
    @Column()
    userId: string

    /**
     * List of banners that are hidden for a user.
     *
     * @type {BannerTemplateEntity[]}
     */
    @ManyToMany(
        () => BannerTemplateEntity,
        (bannerTemplate) => bannerTemplate.hiddenForUsers,
        { cascade: true },
    )
    @JoinTable()
    hiddenBanners: BannerTemplateEntity[]
}

Some banners must be hidden for some users (and, hence, some users do not have to see some banners), so I've designed the database this way. Now I want to query only those banners that are not hidden for user(s) (for example, for me).

Note: userIdsNotHidden is array of strings (uuids) for users for column userId

I've tried something like:

const mainAlias = 'bannerTemplate'
const queryBuilder = this._bannerTemplateRepository.createQueryBuilder(mainAlias)

queryBuilder.where('TRUE')

if (userIdsNotHidden?.length) {
    queryBuilder
        .leftJoin(`${mainAlias}.hiddenForUsers`, userAlias)
        .andWhere(`${userAlias}.userId NOT IN (:...userIdsNotHidden)`, {
            userIdsNotHidden,
        })
}

const bannerTemplates = await queryBuilder.getMany()

But it returns empty array. Here is the second try:

const userAlias = 'user'

queryBuilder.leftJoin(
    `${mainAlias}.hiddenForUsers`,
    userAlias,
    `${userAlias}.userId NOT IN (:...userIdsNotHidden)`,
    { userIdsNotHidden },
)

But now it excludes only users for whome banners were hidden and actually returns all banners.

I've found the following solution for pure SQL query - mysql select rows not in many-to-many relation - however, I don't know to do it with TypeORM (PostgreSQL), because I cannot use pure SQL (it would be ugly and not flexible solution). I also don't know how to query joining table generated by TypeORM (didn't found any relevant information about it)

Any ideas?

How to select rows that are NOT IN many-to-many relation (TypeORM/PostgreSQL)

0 Answers0