TypeORM select entity, except some, where id not equal condition

Question

I Have two entities:

    @Entity()
    export class Point {
    
         @PrimaryGeneratedColumn('uuid')
         id: string;
    
         // some other stuff
 
    }


    @Entity()
    export class Product {
    
         @PrimaryGeneratedColumn('uuid')
         id: string;
    
         @IsOptional()
         @ManyToMany(() => Point)
         @JoinTable()
         prohibitedToSaleOn: Point[];
    
    }

I want to get products, where any object from prohibitedToSaleOn (array of Point's) fulfills the condition

point.id != {idWhatIWant}

So, in final I want to get all product, not banned from sales in selected point. I do something like this:

    return this.productRepository.createQueryBuilder('product')
        .leftJoin('product.prohibitedToSaleOn', 'point')
        .where('point.id != :id', {id})
        .getMany();

But it doesn't work (it should not at all)

I need help with the right request. Thanks =)

P.S. I use PostgreSQL

Answer 1

I'm not sure if you need to use query builder. Here you can find the description of the alternative way to write joins using relations.

To filter them by id not being equal to the one you provide (.where('point.id != :id', {id})), you can write something like find({where: {id: Not(id)}}), where Not is imported from TypeORM.

Answer 2

try to move WHERE condition in to join level

return this.productRepository.createQueryBuilder('product')
        .leftJoin('product.prohibitedToSaleOn', 'point', 'point.id != :id', {id})
        .getMany();

this query should return all products contained in prohibitedToSaleOn join table except for the specified point id.

If you need products that are not banned from sales at the selected point and products that have never been banned, you need query like this:

return this.productRepository.createQueryBuilder('product')
        .leftJoin('product.prohibitedToSaleOn', 'point', '(point.id != :id OR point.id IS NULL)', {id})
        .getMany();